Count If Multiple Tabs and Multiple Criteria

[teacup1787]

I have two tabs. Sheet 5 and Sheet 6. Sheet 5 contains Statements for specific Businesses and Sheet 6 is a summary Spreadsheet.

I am using the following code in the summary sheet.

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

this counts all the statements for a business. So for example Ebay = 4.

But I only want it to count a statement if it has a status of Active. So Ebay should = 2.

I am using Excel 2010, I am hoping that the code above can be extended to include the IF Statement Active part which is missing.

http://www.2shared.com/file/1y_Nz4ng/sample_1.html

Thanks,

 

[RonB1111]

=SUMPRODUCT(COUNTIF(A2, INDIRECT("'"&"Sheet 5"&"'!C2:C12")),--('Sheet 5'!B2:B12="Active"))

 

[Marcelo Branco]

Hi and welcome,

Try this

=COUNTIFS('Sheet 5'!$C:$C,A2,'Sheet 5'!$B:$B,"Active")

M.

 

[DonkeyOte]

x-posted: http://www.excelforum.com/excel-general/827876-countif-multiple-criteria-and-multiple-sheets.html