Forum Discussion
vlookup formula problem
I didn't really understand what you're up to.
but here is an example of a "VLOOKUP, IF; AND" combination ,,,,,
= IF (AND (VLOOKUP ($ A3; Sheet2! $ A: $ I; 9; 0)> = (G3 + I3); (N3 <0)); "Order"; IF (AND (G3> = L3; I3> 0); "Cancel"; ""))
maybe you can adapt it to your needs!
otherwise send your table with an example (w/out sensitive data)!
additional infos:
IF function – nested formulas and avoiding pitfalls
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
I'm so sorry, I'm Italian and my English is not so good ahahah...
I attach an example table.
As you see, I have some names in column C, but not all of them have a number in column F.
I need to count how many times there is a name in C with a number in F (there's no way a name can figure once with a number and then without a number, every single name has ALWAYS or NEVER a number)
Here is an example, I created (in another spreadsheet) a list with an example of all the names I need, and these will be the results:
MATT 0
GEORGE 0
KEVIN 2
LUCAS 1
ANTHONY 0
MICHAEL 1
PETER 0
I'm not even sure which formulas I need, I thought about an IF and a VLOOKUP but it can be everything you want...
Thank you so much
As variant that could be
with
=LET( namesRange, C2:C14, numbersRange, F2:F14, uniqueNames, UNIQUE(namesRange), counts, COUNTIFS(numbersRange,">0",namesRange,uniqueNames), rawResult, IF({1,0},uniqueNames,counts), Result,FILTER(rawResult,uniqueNames<>0), Result)If I understand correctly the picture of you and the translation, I will send you this file as a solution.
Sarei felice di sapere se potessi aiutare.
Nikolino
So di non sapere niente (Socrate)
* Segna e vota gentilmente questa risposta se aiuta, per favore, poiché sarà utile per più membri della Comunità che leggono qui.
Thank you very much for interesting in my problem, but unfortunately it's not what I need (it's not your fault obviously)...
I'll try to explain WHY I need all this.
I have a school of motorbike, and it counts like 800 students. Everyone of these 800 can come whenever they want, once, twice or even more, but everyday of lesson, we can accept only the first 100 who subscribe. I have a list of all the 800 names, but of course not all of them come to EVERY SINGLE day of lessons! So I need to count how many times they came.
How do we know if they came in a specific day? Well of course, we can see his name among the 100 subscribers, but we will count him only IF he has a price next to him (if there is not a price, it means that he subscribed but he didn't come, so we don't need to count him).
I attach an example table with the list of all the names and two days of lessons, and the result I need from this formula.
I saw that you put a formula in column C that gives the sum of all the values of a single name (ex. Kevin has 2 values "50", so next to Kevin I will see 50+50=100, Lucas has "25" and "70" so next to Lucas I will always see 25+75=95, and so on).
But I don't need to see the sum of the values. It doesn't matter for me if they have 50, 25, 70 or whatever. I need just to see how many times they have a value!
Maybe the solution from SergeiBaklan fits more my needs, but I'm not so confident with those "xlfn" and "xlpm" formulas... Is there another easier way to solve it?
Thank you so much guys for your effort!!!!
If you have xlfn that means your version of Excel doesn't support dynamic arrays and these formulas won't work.
In general basis formula could be relatively simple, e.g. in C11
=IF($B11="","",COUNTIFS(J$7:J$19,$B11,K$7:K$19,">0"))and drag it down till end of the list. For the next day copy this formula to the right, adjust ranges and copy again till end of the list, etc.
That will be still a lot of manual work and the main issue is how you structure the data and how you keep it.
For example, in main list you have name "Michael " (with spaces at the end) and in the date list "Michael". Extra spaces could be removed by formulas, but that could be other misprints of you add names manually, like "Michal". Thus you need to check all result which is not far from manual calculations. Solution could data validation drop down lists or another data structuring to use every name only one.
Another issue with the sample is the need to adjust formula for each day since there is no formal logic where to find data for each day. If such logic exists formulas could be adjusted to only copy/paste them without adjustments for each date.
Thus the solution depends on actual data structuring and are you ready to change it or not. We don't know your business and don't know what else do you have in the sheets and how else you use the information.
From formulas point of view most reliable will PivotTable as Detlef_Lewin suggested, but that all depends on how deep the data restructuring will be.
Perhaps your actual data looks totally different and PivotTable works on it as it is.
