Nested IF statement with cell range reference

[Joe Spicer]

Please help.

I have the following two sheets with the following data (actual sheets
contain thousands of records, but this is just a sample):

"Jan" Sheet:
A B C
Unit # New Client Revenue
1 1 1 50
2 1 0 40
3 1 1 20
4 2 1 30
5 2 1 10
6 2 0 60


"New Revenue" Sheet:
A B
Unit # Total Revenue
1 1 "Formula here"
2 2 "Formula here"

On the "New Revenue" sheet I would like to sum the revenue from the "Jan"
sheet based on two conditions: 1) Whether the Unit # on the "Jan" sheet
matches the Unit # in column A on the "New Revenue" sheet. 2) Whether there
is a "1" in the New Client column (Column B) on the "Jan" sheet, indicating
that the revenue is from a new client. If the unit # matches and the client
is new, then I want to calculate the sum of the new revenue. These are just
examples, but on the real sheet there is a lot more data (about 150 different
units with about 20 entries per unit on the "Jan" sheet).

I have written the following formula in cell B1 on the "New Revenue" sheet,
however it does not give me the correct sum:
=sum(if(Jan!$A$1:$A$6=New Revenue!A1,if(Jan!$B$1:$B$6>0,Jan!$C$1:$C$6,0),0))

I should get a sum of 70, but I get 0.

If I copy the formula down to cell B2 I should get a sum of 40, but I get 0.

I am pretty sure this is possible because when I use the Conditional Sum
Wizard to write the formula I get the correct result, however I cannot refer
to the "New Revenue!A1" cell in the formula when using the wizard. Instead I
have to hard code a "1" for the unit # and this will not work when I copy the
formula down for all units. Furthermore, after creating the formula using
the wizard, I am able to see the syntax that the wizard created, but when I
enter the same syntax manually I do not get the same result, which leads me
to believe that the wizard is doing something else behind the scenes.

Does anyone know how I can modify my formula to total the revenue based on
the two conditions that I specified above? It may be a matter of changing a
few things in my formula or it may be an entirely different formula, but I
think it can be done. Any help would be greatly appreciated.

Thanks!

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(Jan!A$1:A$6=A1),--(Jan!B$1:B$6=1),Jan!C$1:C$6)

Copy down as needed.

Biff

 

[Joe Spicer]

Biff,

Thank you so much. I have been working on this all day and your formula
worked.

I do have one question though. I rarely use the SUMPRODUCT formula, so I
looked it up to see exactly what it is doing and it seems that it is just
multiplying the corresponding numbers in each array (that meet the
conditions). This would mean that when it gets to unit # 2 it would multiply
2 (unit #) x 1 (New Client indicator) x 30 (Revenue) = 60. Since multiplying
by the unit # 2 would overstate the Revenue this would not work. I am
assuming that the "--" that you entered into the formula changes the value to
"1". Can you please verify this? Again, thank you so much for your help. I
really appreciate it.

Joe

 

[Biff]

Hi!

You are correct that the arrays are being multiplied together.

This is how it works:

Jan!A$1:A$6=A1 and Jan!B$1:B$6=1 will return arrays of boolean TRUE's and
FALSE's:

TRUE.....TRUE
TRUE.....FALSE
TRUE.....TRUE
FALSE.....TRUE
FALSE.....TRUE
FALSE.....FALSE

The "--" converts those boolean values to either 1 for TRUE, or 0 for FALSE,
so that the arrays now look like this:

1.....1
1.....0
1.....1
0.....1
0.....1
0.....0

Now, include the last array, Revenue:

1.....1.....50
1.....0.....40
1.....1.....20
0.....1.....30
0.....1.....10
0.....0.....60

Then the arrays are multiplied:

1*1*50 = 50
1*0*40 = 0
1*1*20 = 20
0*1*30 = 0
0*1*10 = 0
0*0*60 = 0

Then the results of the array muliplication are summed so that the result of
the formula is 70.

So, your assumption that:

This would mean that when it gets to unit # 2 it would multiply
2 (unit #) x 1 (New Client indicator) x 30 (Revenue) = 60.

Is not correct.

Biff

 

[Joe Spicer]

You're the man Biff. Thanks for the detailed explanation. I will be able to
use this formula a lot in the future and it helps to know how it works. I
really appreciate your help and the way you took the time to explain this.
Thanks.

Joe

 

[Biff]

You'll "discover" that the Sumproduct function is extremely versatile.

Once you learn how to apply Sumproduct you'll never use the Conditional Sum
Wizard again (which is very limited in scope).

Thanks for the feedback!

Biff