# SUMPRODUCT / SUMIF on multiple sheets, multiple criteria - and without using SUMIFS?

1. ## SUMPRODUCT / SUMIF on multiple sheets, multiple criteria - and without using SUMIFS?

I need to sum up data on multiple sheets using two criteria, which I've managed to do in two different ways (below). However I'm trying to remove the SUMIFS() function from either of these methods to enable this workbook to function in Excel version 2003 (sadly still used by some of our guys on site).

I'm also posting this so that there's a simple reference for such a solution (below) if you are using Excel 2007 or later - as I see similar posts on many forums about this, SUMPRODUCT on Multiple Sheets with Multiple Criteria, but they are rarely generic and can be difficult to follow for others.

To explain the formulae below, on each sheet there's three named columns:
1 - Critera1Range
2 - Critera2Range
3 - SumRange

Then there's a list of the sheet tab names (again a named range): ListTabs

Highly recommend anyone to use Named Ranges for these references rather than A4:A20 and B4:B20 type references; especially as if the range changes, say you insert extra rows, the formulae below will not update the references to that range within the INDIRECT function. However if your named ranged updates these formulae will still work.

Some people would call this a 3D array, with columns, rows and sheets being the 3 Dimensions.

First I have an Array formula which works fine (for the uninitiated, Array formulae are generated be pressing CTL+SHFT+ENTER when editing the formula)

{=SUM(SUMIFS(INDIRECT(ListTabs&"!SumRange"),INDIRECT(LstTabs&"!Critera1Range"),Criterion1,INDIRECT(ListTabs&"!Critera2Range"),Criterion2))}

Second I've a SUMPRODUCT (non-array) formula which also works fine:

=SUMPRODUCT(SUMIFS(INDIRECT(ListTabs&"!SumRange"),INDIRECT(ListTabs&"!Critera1Range"),Criterion1,INDIRECT(ListTabs&"!Critera2Range"),Criterion2))

So, can anyone help me switch out the SUMIFS in these functions for another SUMIF/SUMPRODUCT/IF statement (Array formula or not)

A minor addition to the INDIRECT references above:
I've ensured there's no spaces within the sheet tab names within ListTabs. If you have spaces or other odd characters like & in your sheet tab names you need to enclose the reference with the ' and would have an INDIRECT formula like this:
INDIRECT("'"&ListTabs&"!SumRange'")

2. ## Re: SUMPRODUCT / SUMIF on multiple sheets, multiple criteria - and without using SUMIFS?

Sounds like an interesting challenge ... But you've not provided anything to play with.

Regards, TMS

3. ## Re: SUMPRODUCT / SUMIF on multiple sheets, multiple criteria - and without using SUMIFS?

Here's an example workbook, where the two tables in the Summary sheet use the formulae above...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1