Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How Do I created a nested sumif - if this column has this and thi.
I have a spreadsheet that has three columns
Person 1 Area A Total$ Person 1 Area B Total $ Person 2 Area A Total $ How can I setup automatic subtotaling so that I can say if A:A contains Person 1 and B:B ocntains Area A, subtotal Total$ I tried if and SumIF. neither worked. Thanks Gita Mooney |
#2
|
|||
|
|||
One way
=SUMPRODUCT(--(A2:A2000="Person 1"),--(B2:B2000="Area A"),C2:C2000) note that you can't use the full range (A:A) in a sumproduct formula. You might improve the usability by using cell references for your criteria =SUMPRODUCT(--(A2:A2000=E1),--(B2:B2000=F1),C2:C2000) where E1 holds the person and F1 the area, that way you don't need to edit the formula when you change the criteria -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Gita Mooney" <Gita wrote in message ... I have a spreadsheet that has three columns Person 1 Area A Total$ Person 1 Area B Total $ Person 2 Area A Total $ How can I setup automatic subtotaling so that I can say if A:A contains Person 1 and B:B ocntains Area A, subtotal Total$ I tried if and SumIF. neither worked. Thanks Gita Mooney |
#3
|
|||
|
|||
Thanks! I will try that. I was trying the formula but I was using A:A. Now
I know why it did not work. Thanks a bunch Gita "Peo Sjoblom" wrote: One way =SUMPRODUCT(--(A2:A2000="Person 1"),--(B2:B2000="Area A"),C2:C2000) note that you can't use the full range (A:A) in a sumproduct formula. You might improve the usability by using cell references for your criteria =SUMPRODUCT(--(A2:A2000=E1),--(B2:B2000=F1),C2:C2000) where E1 holds the person and F1 the area, that way you don't need to edit the formula when you change the criteria -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Gita Mooney" <Gita wrote in message ... I have a spreadsheet that has three columns Person 1 Area A Total$ Person 1 Area B Total $ Person 2 Area A Total $ How can I setup automatic subtotaling so that I can say if A:A contains Person 1 and B:B ocntains Area A, subtotal Total$ I tried if and SumIF. neither worked. Thanks Gita Mooney |
#4
|
|||
|
|||
Hi,
Pivot table seems to be the better option. -----Original Message----- One way =SUMPRODUCT(--(A2:A2000="Person 1"),--(B2:B2000="Area A"),C2:C2000) note that you can't use the full range (A:A) in a sumproduct formula. You might improve the usability by using cell references for your criteria =SUMPRODUCT(--(A2:A2000=E1),--(B2:B2000=F1),C2:C2000) where E1 holds the person and F1 the area, that way you don't need to edit the formula when you change the criteria -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Gita Mooney" <Gita wrote in message ... I have a spreadsheet that has three columns Person 1 Area A Total$ Person 1 Area B Total $ Person 2 Area A Total $ How can I setup automatic subtotaling so that I can say if A:A contains Person 1 and B:B ocntains Area A, subtotal Total$ I tried if and SumIF. neither worked. Thanks Gita Mooney . |
#5
|
|||
|
|||
You can also use autofilter and subtotal function on the total column. When
you filter on person1 or person2 the total will change. "gita" wrote: Thanks! I will try that. I was trying the formula but I was using A:A. Now I know why it did not work. Thanks a bunch Gita "Peo Sjoblom" wrote: One way =SUMPRODUCT(--(A2:A2000="Person 1"),--(B2:B2000="Area A"),C2:C2000) note that you can't use the full range (A:A) in a sumproduct formula. You might improve the usability by using cell references for your criteria =SUMPRODUCT(--(A2:A2000=E1),--(B2:B2000=F1),C2:C2000) where E1 holds the person and F1 the area, that way you don't need to edit the formula when you change the criteria -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Gita Mooney" <Gita wrote in message ... I have a spreadsheet that has three columns Person 1 Area A Total$ Person 1 Area B Total $ Person 2 Area A Total $ How can I setup automatic subtotaling so that I can say if A:A contains Person 1 and B:B ocntains Area A, subtotal Total$ I tried if and SumIF. neither worked. Thanks Gita Mooney |
#6
|
|||
|
|||
That worked beautifully. However, the summary I am trying to create is on
the second sheet. When I try to add the name of the first sheet, it bombs. For example instead of A2:A2000, I have Sheet1!a2:a2000. Am I doing something wrong? Thanks in advance Gita "Peo Sjoblom" wrote: One way =SUMPRODUCT(--(A2:A2000="Person 1"),--(B2:B2000="Area A"),C2:C2000) note that you can't use the full range (A:A) in a sumproduct formula. You might improve the usability by using cell references for your criteria =SUMPRODUCT(--(A2:A2000=E1),--(B2:B2000=F1),C2:C2000) where E1 holds the person and F1 the area, that way you don't need to edit the formula when you change the criteria -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Gita Mooney" <Gita wrote in message ... I have a spreadsheet that has three columns Person 1 Area A Total$ Person 1 Area B Total $ Person 2 Area A Total $ How can I setup automatic subtotaling so that I can say if A:A contains Person 1 and B:B ocntains Area A, subtotal Total$ I tried if and SumIF. neither worked. Thanks Gita Mooney |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|