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  

