SUMIF across a range of worksheets

[Guest]

Hi

I am trying to write a SUMIF formula that will sum across a range of
worksheets in a workbook. I never know how many worksheets there will be in
this range. The formula I am trying is
=SUMIF(Clin1:ClinEND!K:K,NOTES!B8,Clin1:ClinEND!U:U).

Can anyone help? If this is not possible using a SUMIF, is there another
formula that would work?

Thanks

Mike

 

[Don Guillett]

try this where you are summing sheet 2,3,4, & 5

=SUM(SUMIF(INDIRECT("Sheet"&{2,3,4,5}&"!A1:A17"),"a",INDIRECT("Sheet"&{2,3,4
,5}&"!B1:B17")))
=======
One way.
Put the sumif on each sheet with an indirect reference to d12 of the master.
then use
=sum(sheet1:sheet21!a2) where a2 in your sumif formula.

One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select all>type the
formula in the cell desired>after the error msg>delete from the master and
use the sum in para 1.

 

[Frank Kabel]

Hi
lets assume you have a list of all your worksheets in the range X1:X10
try:
=SUMPRODUCT(SUMIF(INDIRECT("'" & X1:X10 &
"'!K:K"),NOTES!B8,INDIRECT("'" & X1:X10 & "'!U:U")))

 

[Domenic]

Assuming that you currently have 10 sheets named Clin1, Clin2, etc...

=SUMPRODUCT(SUMIF(INDIRECT("'Clin"&ROW(INDIRECT("1:10"))&"'!K2:K1000"),B8,INDIRECT("'Clin"&ROW(INDIRECT("1:10"))&"'!U2:U1000")))

OR

=SUMPRODUCT(SUMIF(INDIRECT("'Clin"&ROW(INDIRECT("1:"&C8))&"'!K2:K1000"),B8,INDIRECT("'Clin"&ROW(INDIRECT("1:"&C8))&"'!U2:U1000")))

...where C8 contains the number of sheets you currently have. So whe
you add another sheet, let's say you add sheet Clin11, enter 11 in C
and that sheet will automatically be taken into account.

Hope this help!