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

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 alltype the
formula in the cell desiredafter the error msgdelete from the master and
use the sum in para 1.

--
Don Guillett
SalesAid Software

"Mike@Q" wrote in message
...
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

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")))

--
Regards
Frank Kabel
Frankfurt, Germany

"Mike@Q" schrieb im Newsbeitrag
...
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

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(INDIR ECT("1:10"))&"'!U2:U1000")))

OR

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

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

Hope this help!

Mike@Q Wrote:
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


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=320097