SOLVED

Need help with very complex formula (conditional/if/find/lookup/sum?)

Copper Contributor

I'm looking for some help creating a complex formula. As in, so complex I am not even sure if this is doable. Let me first explain how the file is built up (I can't share it, as it contains confidential information regarding our customers). My file is divided into two sheets. The first sheet's columns are:Sheet 1.PNG

 

 The second sheet's columns are:Sheet 2.1.PNGSheet 2.2.PNG

 

 Now I need to create a table that shows me how much revenue one Account Manager has made our company any given month. Then, I also need to be able to divide that revenue into NEW and OLD customers (i.e. those who became customers in 2017 and those who became customers before 2017).

  

For example

I want to see how much Account Manager Ron has earned for us in Jan 17. The formula needs to find all the customers that belong to Ron (Sheet 1 Column D) and sum up their revenue (Sheet 2 Column P). Then it needs to find all the customers that belong to Ron, filter to include only those who became customers in 2017 (Sheet 1 Column C) and sum up their revenue. Then the same but filter only those who became customers in 2015 and 2016. The subsequent table should look kind of like this:

Table.PNG

 

 

 So for the Revenue TOTAL, it's somethings like:

IF Sheet 1 Column D equals Ron then FIND matching Column A to Sheet 1 on Sheet 2 and SUM Sheet 2 Column P or IF Sheet 1 Column D equals Ron then SUM Sheet 2 Column P of the same rows.

 

The other two are even more difficult. They need something like:

IF Sheet 1 Column D equals Ron AND IF Sheet 1 Column C equals 2017 then FIND /SUM ......

IF Sheet 1 Column D equals Ron AND IF Sheet 1 Column C equals 2016 or 2015 then FIND / SUM .......

 

So as you can see this is incredibly complex and I can't manage on my own. Please let me know if you can help with this, or let me know if what I'm asking is simply impossible. I am not attached to the file so if I need to make any (drastic) changes to its structure or rearrange columns to make it work, that would be completely fine. Any help or answer is appreciated!!!!

 

Thanks.

4 Replies
best response confirmed by Noortje Vollenberg (Copper Contributor)
Solution

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

 

CustomersSheet.JPG

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!

1 best response

Accepted Solutions
best response confirmed by Noortje Vollenberg (Copper Contributor)
Solution

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

 

CustomersSheet.JPG

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.

 

 

 

 

 

View solution in original post