J
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!
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!