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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 01:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com