Count number of time text appears with multiple criteria

**Ken_NJ** · 01-08-2013, 01:45 PM

Hi all! Here is the setup to my problem. I am using Excel 2003.
If I have 2 worksheets, sheet 2 has text in columns A&B. Sheet1 has text in cell A2 and B2. I need a formula in cell C3 to count how many times sheet2-columnA appears, only if sheet2 A&B matches sheet1 A&B.

This works when the data is on the same worksheet.
{=SUM((AH7:AH12=AC12)*(AI7:AI12=AC6))}

But does not work for me when the data is on the second worksheet
{=SUM((PatchList!$B$2:$B$35000=AC9)*(PatchList!$J$2:$J$35000=$AC$6))}

I've looked for doing this with Countif array's and Sumif, but have not come up with the proper syntax to any of the formula's.

**Moo the Dog** · 01-08-2013, 01:57 PM

Untested, but you can try this:

Regular formula (just hit enter)

Formula:

Please Login or Register  to view this content.

- Moo

**Ken_NJ** · 01-08-2013, 02:00 PM

I get #N/A

**Moo the Dog** · 01-08-2013, 02:03 PM

This may seem like a silly question, but is there a sheet with the name 'PatchList' in your workbook (spelled exactly that way without spaces)?

If there is, perhaps you could upload a copy of your workbook, with any sensitive data removed so we can take a look to see what the problem may be.

- Moo

**Ken_NJ** · 01-08-2013, 02:18 PM

I don't use spaces in my tabs, so yes it is exactly PatchList. Well this thing is 27mb, I can try and remove what I can to make it smaller and remove unnecessary data.

**Ken_NJ** · 01-08-2013, 02:53 PM

I have the file down to 2.5 meg and still too large to upload it. But in trying to lighten up the file, both your SUMPRODUCT and my SUM statement work now. It might have something to do with the data in the J column on PatchList being a VLookup from another sheet. In the lighter file I had to replace the VLookup J column with actual data and now both of the SUM statements are working.