SUMIF

[Dave]

How can I sum amounts across multiple worksheets based on
a condition of a different cell also across the multiple
worksheets? I tried the following without success:
SUMIF(first:last!K4,">0",first:last!K5)

I am trying to look at K4 in each worksheet and if it is
greater than 0 add K5 to the sum.

Thanks

 

[Peo Sjoblom]

One way

=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A6&"'!K4"),">0",INDIRECT("'"&A2:A6&"'!K5")
))

where A6:A6 is a range with the name of the sheets you want to sum, if you
would use the default sheet names it could be hard coded like

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{1,2,3}&"'!K4"),">0",INDIRECT("'Sheet"&{
1,2,3}&"'!K5")))

or

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(1:3)&"'!K4"),">0",INDIRECT("'Sheet"&
ROW(1:3)&"'!K5")))

will sum K5 on sheets 1, 2 &3 where K4 is greater than 0