Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mike@Q
 
Posts: n/a
Default SUMIF across a range of worksheets

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

  #2   Report Post  
Don Guillett
 
Posts: n/a
Default

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



  #3   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


  #4   Report Post  
Domenic
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use SUMIF to return sums between two values located in cells ScottBerger Excel Worksheet Functions 2 April 23rd 23 09:05 PM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM
SUMIF with only one criteria cell within range Stan Excel Discussion (Misc queries) 5 December 16th 04 11:12 PM
named range refers to: in a chart Spencer Hutton Excel Discussion (Misc queries) 1 December 14th 04 11:15 PM
Macro with Range of Worksheets Chris E. New Users to Excel 8 December 3rd 04 05:56 PM


All times are GMT +1. The time now is 07:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"