Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 31st 04, 05:54 PM
Gita Mooney
 
Posts: n/a
Default 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   Report Post  
Old October 31st 04, 06:10 PM
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
Old October 31st 04, 11:25 PM
gita
 
Posts: n/a
Default

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   Report Post  
Old November 1st 04, 08:41 AM
 
Posts: n/a
Default

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   Report Post  
Old November 1st 04, 02:14 PM
Gary Rowe
 
Posts: n/a
Default

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   Report Post  
Old November 1st 04, 04:18 PM
gita
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 09:41 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017