Counting Two Sets of Criteria Between Multiple Spreadsheets

Hi,

I have data in 15 different worksheets in one workbook. I am trying to count data when it matches two separate sets of criteria (status & grade). Status is in the H column in the individual spreadsheets and Grade is in the B column in the individual spreadsheets. I have created a calculations page where l listed the separate spreadsheet names (column A), grade options (COLUMN C), and status options (COLUMN B).

I have entered the below formula but get the #REF error message. I cannot figure out what the issue is. Please help! Thank you in advance.

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$4:$A$18&"'!"&"H3:H100"),$B$4,INDIRECT("'"&$A$4:$A$18&"'!"&"B3:B100"),C4))

15 separate files, or 15 sheets in the one file?

Try this:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$4:$A$18&"'!H3:H100"),$B$4,INDIRECT("'"&$A$4:$A$18&"'!B3:B100"),C4))

The attached sheet shows it working. The ranges are not identical to yours; but you can adapt it....


=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$4:$A$13&"'!A1:A100"),$B$4,INDIRECT("'"&$A$4:$A$13&"'!B1:B100"),C4))

Can you post your real sheet? If not, PM me...


Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.