Forum Discussion
Need help with very complex formula (conditional/if/find/lookup/sum?)
Hi Noortje,
Better to have your data bit more structured and use at least some elements of data model (e.g tables with relationships), but we have what we have. And not sure i understood how you'd like to structure the results, thus here is quite row prototype to illustrate the approach on simple data
Left part imitates your first sheet, after that second one and finaly results for Jan column.
Formula for total is
=SUMPRODUCT($H$2:$H$600*NOT(ISNA(MATCH($F$2:$F$600,($A$2:$A$600)*($D$2:$D$600=$K2),0))))
Part under MATCH filters customers codes in second sheet (F:F) selecting all customers (A:A) for whom name of the manager (D:D) is in K2. ISNA returns TRUE (1) if no matches and zero otherwise, with NOT we convert 1 to 0 and opposite. After that you we multiple that array of 1 and 0 on revenue (H:H), excluding not needed by multiplying on 0, and finally sum the result (SUMPRODUCT).
Under the MATCH we may add as many filters as needed, e.g for new revenue (C:C = 2017) it'll be
=SUMPRODUCT($H$2:$H$600*NOT(ISNA(MATCH($F$2:$F$600,($A$2:$A$600)*($D$2:$D$600=$K2)*($C$2:$C$600=2017),0))))
Please see the file attached. Again, that's only prototype.
Hi Noortje,
Better to have your data bit more structured and use at least some elements of data model (e.g tables with relationships), but we have what we have. And not sure i understood how you'd like to structure the results, thus here is quite row prototype to illustrate the approach on simple data
Left part imitates your first sheet, after that second one and finaly results for Jan column.
Formula for total is
=SUMPRODUCT($H$2:$H$600*NOT(ISNA(MATCH($F$2:$F$600,($A$2:$A$600)*($D$2:$D$600=$K2),0))))
Part under MATCH filters customers codes in second sheet (F:F) selecting all customers (A:A) for whom name of the manager (D:D) is in K2. ISNA returns TRUE (1) if no matches and zero otherwise, with NOT we convert 1 to 0 and opposite. After that you we multiple that array of 1 and 0 on revenue (H:H), excluding not needed by multiplying on 0, and finally sum the result (SUMPRODUCT).
Under the MATCH we may add as many filters as needed, e.g for new revenue (C:C = 2017) it'll be
=SUMPRODUCT($H$2:$H$600*NOT(ISNA(MATCH($F$2:$F$600,($A$2:$A$600)*($D$2:$D$600=$K2)*($C$2:$C$600=2017),0))))
Please see the file attached. Again, that's only prototype.
Hi Sergei, Thanks for your help! This looks exactly like what I need. My only problem is that it doesn't seem to work? When I download your file I can see that the formula works, but when I apply it to my own file (which I restructured according to your prototype) it doesn't. It just shows the full formula.
I use the following formula:
=SOMPRODUCT($K$2:$K$600*NIET(ISNB(VERGELIJKEN($I$2:$I$600;($A$2:$A$600)*($C$2:$C$600=$R3);0))))
The formula automatically adjusted to Dutch (matching the settings on my excel), so I know the functions are correct and that I need to use semi-collon instead of comma. And it finds the correct cells. So the only thing that I can think of what could be a problem is the * signs. I have never used them in a formula except for basic maths (multiply), so I don't know what function it has and if it needs to be translated to a Dutch alternative as well. A quick Google search couldn't tell me either.
If you could either explain what it needs to do I can perhaps do a more detailed search, or if someone else can answer that would be a big help as well! Or if there is something else I am doing wrong, feel free to let me know.
Thank you
Hi Noortje,
The * is the equivalent of AND function. If <a> and <b> are two logical expressions when =AND(a,b) and =(a*b) return the same results. It shall work in any locale.
Perhaps your data for "second" part starts from row 3, not from row 2. When you have to change $K$2:$K$600 and $I$2:$I$600 on $K$3:$K$600 and $I$3:$I$600 accordingly.
Could you please send the screenshot how is your data placed (just column names, without actual data)?
Hi Sergei,
Thank you so much for your help!
Your explanation of the END function made me take another close look to see what else it could possibly be. Turns out that the cell properties were set to "Text". As soon as I set it to "Currencies" it showed me the results of the formula you gave me. A silly little mistake on my part. The second formula also works.
Once again, thank you so much!