Formula for counting values across a range using multiple criteria across multiple sheets

Hi,

Will be the layout of each sheet to be considered be identical?

Regards

On Sheet1 in column C you have the header "Select" but the range below is empty.

What is supposed to go there? The options?

How many sheets are there? What are the REAL sheet names?

It looks like you're going to need/use a lot of these formulas. The formula to do this is very complex and extremely inefficient. This may impact the performance of your file.

Hi,

Go to Name Manager and define a new name, Sheets say, as:

={"Sheet2","Sheet3"}

(Or whatever the relevant tab names in your actual set-up are.)

Exit Name Manager.

The formula in D8 of your summary table is then:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!$I4:$I1000"),INDIRECT("'"&Sheets&"'!$E4:$E1000"),Sheet1!$B8,INDIRECT("'"&Sheets&"'!$F4:$F1000"),Sheet1!D$7))

Copy down and across as required.

Obviously amend the end range reference (I used 1000) as necessary.

Regards

Ah, yes. Apologies. Forgot about the 2003 part. Then you'll need this version:

=SUMPRODUCT((T(OFFSET(INDIRECT("'"&Sheets&"'!$E4"),ROW($1:$1000),,,))=Sheet1!$B8)*(N(OFFSET(INDIRECT("'"&Sheets&"'!$F4"),ROW($1:$1000),,,))=Sheet1!D$7)*(N(OFFSET(INDIRECT("'"&Sheets&"'!$I4"),ROW($1:$1000),,,))))

Regards

Sorry - don't understand what you mean. If the data goes beyond row 1004 (starting row of 4 plus my arbitrary 1000) in any of your sheets then adjust the 1000 in the ROW($1:$1000) construction appropriately.

It is not of huge importance that this number match precisely the maximum row number containing data in all of your tabs; only that it is sufficiently large (though I wouldn't recommend setting it to be unnecessarily large).

Regards

Using ROW(...) leaves the formula vulnerable to new row insertions on the sheet that contains the formula.

If you will never insert new rows then it'll work just fine. If you might insert new rows then you should make it more robust like this...

ROW(INDIRECT(...))

However, this is also vulnerable to row insertions in the data ranges.

So, you're damned if ya do and you're damned if ya don't!

Thanks, Tony.

And yes, I did make the choice between assuming that you would not be inserting rows (I think, in general, that a spreadsheet in which row insertion is a required feature is a poorly-designed spreadsheet) and assuming that you would and therefore offering you a "volatile" solution with INDIRECT.

However, as Tony says, if you must insist upon having the option of allowing row insertions, then follow his advice and wrap the ROW() constructions in an INDIRECT. I'm sure we'd be happy to show you how if you are not sure.

Regards