SUMIF help

[Louie]

I am trying to sum 5 sheets into a master sheet by client using the following
formula, I am not sure of how to add all the sheets in the workbook. This is
what I got so far for the master sheet

=SUMIF(Sheet1!A:A,"approved",Sheet1!C:C)

and that works fine for one sheet, how do I apply it so it adds all five
sheets at one time for "approved"
Please help.!
thank you

 

[Don Guillett]

One way is to put the same formula in the same place on each sheet and then
=sum(sheet1:sheet21!a2)

 

[Don Guillett]

Also look here
http://tinyurl.com/ycccc7

 

[RagDyeR]

To start, you'll need a list of your sheet names.

Say in Z1 you enter
Sheet1
And drag down to create your Sheet1 to Sheet5 list in Z1 to Z5.

Then, try this formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z5&"'!A:A"),"Approved",INDIRECT("'"&Z1:Z5&"'!C:C")))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I am trying to sum 5 sheets into a master sheet by client using the
following
formula, I am not sure of how to add all the sheets in the workbook. This
is
what I got so far for the master sheet

=SUMIF(Sheet1!A:A,"approved",Sheet1!C:C)

and that works fine for one sheet, how do I apply it so it adds all five
sheets at one time for "approved"
Please help.!
thank you

 

[Louie]

=sum(sheet1:sheet21!a2)
this worked,
thank you so much for your help

 

[Peo Sjoblom]

The formula that Ragdyer posted will do just that
Put all the sheet names in Z1:Z5 (adapt to fit if you have more or less
sheets than 5. Change to the correct columns and replace "approve" with
your criteria



Regards,

Peo Sjoblom

 

[T. Valko]

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z5&"'!A:A"),"Approved",INDIRECT("'"&Z1:Z5&"'!C:C")))

What are you having a problem with?

In the above formula: Z1:Z5 is a range of cells that hold your sheet names.

The above formula modified for your situation:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z5&"'!C:C"),"THHN",INDIRECT("'"&Z1:Z5&"'!D")))

Biff