Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Stephen POWELL
 
Posts: n/a
Default SUMPRODUCT across worksheets

I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to obtain
results not possible with SUMIF. However, I would now like to apply this to,
say, all D5 cells in many contiguous worksheets. I get error results. Can
someone confirm that this is not possible to do? Any suggestions for a way
around this?
Many thanks,
Stephen Powell
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

If you only have one criteria like in a sumif and either sum the same range
or another range you can use this:
First you need to create a list with ALL (not like in 3-D excel first and
last) sheet names or if they have the same aplha name plus numbers you can do
it as well.
1. if they all have different names and you put all sheet names in range
H1:H20
and let's say you want to sum B1:B100 in all sheets where A1:A100 is criteria1
then you can use this

=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A1 00"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B10 0")))


if the names are identical text plus different numbers like Sheet1, Sheet2
amd so one then you can use

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet" &ROW(INDIRECT("1:20"))&"'!B1:B100")))

if you need more criteria and more ranges may I recommend Morefun by Laurent
Longre

It's an excellent add-in that has some nice 3-D features

http://longre.free.fr/english/

here's a description in English


http://www.rhdatasolutions.com/morefunc/


Regards,

Peo Sjoblom

"Stephen POWELL" wrote:

I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to obtain
results not possible with SUMIF. However, I would now like to apply this to,
say, all D5 cells in many contiguous worksheets. I get error results. Can
someone confirm that this is not possible to do? Any suggestions for a way
around this?
Many thanks,
Stephen Powell

  #3   Report Post  
Stephen POWELL
 
Posts: n/a
Default

Peo:
Thank you kindly. Your solution works nicely. I've never used INDIRECT
before and it is not intuitively obvious to me why it works - I will have to
research this function to understand it.
Thanks again.
Stephen

"Peo Sjoblom" wrote:

If you only have one criteria like in a sumif and either sum the same range
or another range you can use this:
First you need to create a list with ALL (not like in 3-D excel first and
last) sheet names or if they have the same aplha name plus numbers you can do
it as well.
1. if they all have different names and you put all sheet names in range
H1:H20
and let's say you want to sum B1:B100 in all sheets where A1:A100 is criteria1
then you can use this

=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A1 00"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B10 0")))


if the names are identical text plus different numbers like Sheet1, Sheet2
amd so one then you can use

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet" &ROW(INDIRECT("1:20"))&"'!B1:B100")))

if you need more criteria and more ranges may I recommend Morefun by Laurent
Longre

It's an excellent add-in that has some nice 3-D features

http://longre.free.fr/english/

here's a description in English


http://www.rhdatasolutions.com/morefunc/


Regards,

Peo Sjoblom

"Stephen POWELL" wrote:

I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to obtain
results not possible with SUMIF. However, I would now like to apply this to,
say, all D5 cells in many contiguous worksheets. I get error results. Can
someone confirm that this is not possible to do? Any suggestions for a way
around this?
Many thanks,
Stephen Powell

  #4   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

My pleasure, thanks for the feedback

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Stephen POWELL" wrote in message
...
Peo:
Thank you kindly. Your solution works nicely. I've never used INDIRECT
before and it is not intuitively obvious to me why it works - I will have
to
research this function to understand it.
Thanks again.
Stephen

"Peo Sjoblom" wrote:

If you only have one criteria like in a sumif and either sum the same
range
or another range you can use this:
First you need to create a list with ALL (not like in 3-D excel first and
last) sheet names or if they have the same aplha name plus numbers you
can do
it as well.
1. if they all have different names and you put all sheet names in range
H1:H20
and let's say you want to sum B1:B100 in all sheets where A1:A100 is
criteria1
then you can use this

=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A1 00"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B10 0")))


if the names are identical text plus different numbers like Sheet1,
Sheet2
amd so one then you can use

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet" &ROW(INDIRECT("1:20"))&"'!B1:B100")))

if you need more criteria and more ranges may I recommend Morefun by
Laurent
Longre

It's an excellent add-in that has some nice 3-D features

http://longre.free.fr/english/

here's a description in English


http://www.rhdatasolutions.com/morefunc/


Regards,

Peo Sjoblom

"Stephen POWELL" wrote:

I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to
obtain
results not possible with SUMIF. However, I would now like to apply
this to,
say, all D5 cells in many contiguous worksheets. I get error results.
Can
someone confirm that this is not possible to do? Any suggestions for a
way
around this?
Many thanks,
Stephen Powell



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
Number of worksheets Mark Excel Discussion (Misc queries) 1 January 18th 05 11:05 AM
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
Assigning Cells in worksheets to other data in other worksheets. David McRitchie Excel Discussion (Misc queries) 0 November 27th 04 07:15 PM
adding two sumproduct formulas together ski2004_2005 Excel Worksheet Functions 1 November 12th 04 10:08 PM
Sort/Link Worksheets Debby Excel Worksheet Functions 0 November 12th 04 08:50 PM


All times are GMT +1. The time now is 11:54 AM.

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"