SUMIF across Worksheets?

[David]

Is there a way to use SUMIF across multiple worksheets in
one workbook. I want to sum all the Positive values only
from one cell, in multiple workbooks.

eg. SUMIF(Worksheet1:Worksheet35!$H$111>"0")

Is there a way to do this?

[Jason Morin]

One way:

=SUMPRODUCT(((N(INDIRECT("Worksheet"&ROW(INDIRECT("1:35"))
&"!H1"))>0)*(N(INDIRECT("Worksheet"&ROW(INDIRECT("1:35"))
&"!H1")))))

HTH
Jason
Atlanta, GA

>.
>

[Jason Morin]

Forgot to mention that I used H1 rather than H111 when
testing.

Jason

>.
>

[Peo Sjoblom]

If indeed you use the name worksheet you can use

=SUMPRODUCT(SUMIF(INDIRECT("'Worksheet"&ROW(1:35)&"'!H111"),">0"))

If you have all individual names for the sheets it is more work involved,
one way would be to make a list out of sight on the summary sheet that
contains the sheet names and then refer to the list range

=SUMPRODUCT(SUMIF(INDIRECT("'"&list_range&"'!H111"),">0"))

I assumed you meant multiple worksheets when you said multiple workbooks

--

Regards,

Peo Sjoblom

David <ds...@shaw.ca> wrote in message
news:004501c2cbaa$9dd38880$d5f82ecf@TK2MSFTNGXA12...

[David]

Thanks, yes I did mean multiple worksheets (within one
workbook) and they are all individually named.

Seems like a lot more work to do a SUMIF (of a cell) then
a SUM when multiple worksheets are involved. I'll give it
a try.
Thanks again.

>-----Original Message-----
>If indeed you use the name worksheet you can use
>
>=SUMPRODUCT(SUMIF(INDIRECT("'Worksheet"&ROW(1:35)&"'!
H111"),">0"))
>
>If you have all individual names for the sheets it is
more work involved,
>one way would be to make a list out of sight on the
summary sheet that
>contains the sheet names and then refer to the list range
>
>=SUMPRODUCT(SUMIF(INDIRECT("'"&list_range&"'!H111"),">0"))
>
>I assumed you meant multiple worksheets when you said
multiple workbooks
>

>Regards,
>Peo Sjoblom