Countif Multiple Criteria and Multiple Sheets

I currently have this forumula:

=SUMPRODUCT(COUNTIF(A4, INDIRECT("'"&"Sheet5"&"'!A2:A35"))) + SUMPRODUCT(COUNTIF(INDIRECT("'"&"Sheet5"&"'!B2:B35"), "Active"))

I am trying to have a count of all the statements that contains the value in A4 which is "EBAY" and have a status of "Active" from a list of 1000 statements. Currently this counts all the statements of Ebay and then adds on top all the Active a total of 170. I in fact want the value 8 which is the open ebay statements.

So I need this to only count if both criteria are met. This is across multiple sheets hence the sumproduct.

Any Ideas?

Hi,

Thanks here is the attached file, FYI I do not want any extra columns added.

sample_1.xlsx

In Excel 2010 the {} seem to be removed and then the forumla does not work, for example the first column returns 1 instead of 2. Why would this be?

Originally Posted by DonkeyOte

Given previous upload in .xlsx format we assume XL2007 or above thereby permitting use of COUNTIFS

First post implies you're aggregating in 3D form:

Please Login or Register  to view this content.

where A1:A4 holds list of 4 sheet names to be aggregated in unison.

I do not understand this bit?? I have 2 sheets "where A1:A4 holds list of 4 sheet names to be aggregated in unison".

I was hoping to expand this:

=SUMPRODUCT(COUNTIF(A2, INDIRECT("'"&"sheet 5"&"'!'Sheet 5'!C2:C12")))

to include only count if b2:b12 is also "active"

The code is actually currently =SUMPRODUCT(COUNTIF(A2, INDIRECT("'"&"Sheet 5"&"'!C2:C12"))) this counts all the statements for a business, I only want it to count those statements which are are active.

I hae attached the spreadsheet again which shows it more clearly. It is Excel 2010 which I am usuing.

sample_1.xlsx