Excel 2007 : Problem with sumproduct across muliple sheets

**FDibbins** · 04-13-2012, 04:56 PM

Hi all

I am trying to teach myself how to use the sumproduct() formula, and have constant problems with it. Sometimes i can get it to do what i want, other times im partially sucessful, and other times it "just dont like me" lol

one example that i am dealing with right now is attached. I have a summary sheet that feeds from sheet1 and sheet2. in Summary C2 i have the SP referencing just sheet1, and it works fine. in summary C3, i have the identical formula, except i now reference sheet1:sheet2...and it doesnt work (returns #name?)

what am i doing wrong, or is sumproduct unable to span multiple sheets in this manner

**Pete_UK** · 04-13-2012, 05:38 PM

Originally Posted by FDibbins

what am i doing wrong, or is sumproduct unable to span multiple sheets in this manner

Yes, this is the problem, and it also applies to SUMIF, COUNTIF etc. It's easier to set up an SP formula in the same cell in each sheet, e.g. X1 (group the sheets together and do it once, then un-group the sheets), and then you can do:

=SUM(first:last!X1)

in your summary sheet.

Hope this helps.

Pete

**FDibbins** · 04-13-2012, 05:55 PM

hmm ok, thx for the reply

, and i fully understand what you are suggesting

However, that will be very resourse-hungry tho, my sample showed only 2 sheets with at from B1:G11. the "live" file has 15 or more sheets, with a range up to BM300 - and growing. So a "summary" on each sheet may not be a practical sollution.

any other suggestions? (from any1 lol)

**Pete_UK** · 04-13-2012, 06:18 PM

If all your sheets have the same structure (as in the example), then you don't need to use SUMPRODUCT. You can put this formula in C2 of the summary sheet:

=SUM(Sheet1:Sheet2!C2)

then just copy across and down. In your live data you would have Sheet1:Sheet15.

You can also consider using COUNTIFS (and SUMIFS) instead of SP, and these are faster and less resource-hungry than SP and other array formulae, but as I say, you don't really need them in this situation.

Hope this helps.

Pete