How to use SUM and SUMPRODUCT across a range of sheets

Lamtek 0 Reputation points
2026-06-11T12:49:53.16+00:00

I have a range of work sheets with product codes in the cells A13 - A45 and Colours of products in the cells B12-F12

I have created a summary sheet for these work sheets that counts the values in all cells where the product and cplour match the values entered on the summary sheet.

I would like to summaries multiple job sheets on to the summary sheet by using the range of sheets rather than having a formula for each sheet and adding them together.

The basic formula is

=SUM(SUMPRODUCT(SHEET1!$B$13:$F$45*(SHEET1!$B$12:$F$12=B$12)*(SHEET1!$A$13:$A$45=$A13)))

At the moment the only solution I have found is to add the same formula together for each sheet I want to summaries i.e.

=SUM(SUMPRODUCT(SHEET1!$B$13:$F$45*(SHEET1!$B$12:$F$12=B$12)(SHEET1!$A$13:$A$45=$A13)))+=SUM(SUMPRODUCT(SHEET2!$B$13:$F$45(SHEET2!$B$12:$F$12=B$12)*(SHEET2!$A$13:$A$45=$A13)))

I know I should be able to simply use the range SHEET1:SHEET25 but I can't find a solution that works.

Can someone help please

Microsoft 365 and Office | Excel | Other | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest

  1. AI answer

    2026-06-11T12:50:07.43+00:00

    3-D sheet ranges such as Sheet1:Sheet25 cannot be used inside SUMPRODUCT, because 3‑D references are only supported by a limited set of functions (for example SUM, AVERAGE, MIN, MAX, etc.) and are not valid in array formulas like SUMPRODUCT.

    From the documentation:

    • 3‑D references (e.g. =SUM(Sheet2:Sheet13!B5)) can be used with functions such as SUM, AVERAGE, COUNT, MAX, MIN, PRODUCT, STDEV.P, STDEV.S, VAR.P, VAR.S, etc.
    • 3‑D references cannot be used in array formulas.

    SUMPRODUCT is an array-based function, so a construct like:

    =SUMPRODUCT(Sheet1:Sheet25!$B$13:$F$45*...)

    is not supported.

    Because of this limitation, the approach of repeating SUMPRODUCT for each sheet and summing the results is the supported method when staying purely in worksheet formulas, for example:

    =SUM(
  SUMPRODUCT(Sheet1!$B$13:$F$45*(Sheet1!$B$12:$F$12=B$12)*(Sheet1!$A$13:$A$45=$A13)),
  SUMPRODUCT(Sheet2!$B$13:$F$45*(Sheet2!$B$12:$F$12=B$12)*(Sheet2!$A$13:$A$45=$A13)),
  ...
)

    There is no supported way, using only standard worksheet functions, to combine SUMPRODUCT with a Sheet1:Sheet25 3‑D sheet range into a single compact formula.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.