Sumproduct with multiple criteria using non numerical values

Have you tried to limit the range for instance G:G to G1:G1000?

Hi,

Are you sure that the rows that you believe meet these criteria actually do? There are no extra spaces in either the search strings in Stats!A2 or Stats!B1 or the ranges being searched, for example?

Regards

that should work providing you are going to sum values in col E(note the reason it gave value error is that pre excel 2007 sumproduct doesn't accept whole range references)
if you just want to count number of rows wher the 2 criteria are met
=SUMPRODUCT(('testimony Extract Report'!G2:G1000=Stats!B1)*('testimony Extract Report'!I2:I1000=Stats!A2))

whats in column e?

I think we may need to see an actual workbook to see where the issue lies.

Obviously replace any confidential/sensitive information with dummy data if necessary.

Regards

SUMPRODUCT cannot count unique value. Seem it be a unique value counting with multi-criteria.

Just to be clear: you want a total count of unique column E entries whose corresponding entries in the G and I columns also satisfy the criteria you outline?

Regards

Then you'll need something like this array formula**:

=SUM(IF(FREQUENCY(IF('testimony Extract Report'!G2:G1000=Stats!B1,IF('testimony Extract Report'!I2:I1000=Stats!A2,MATCH('testimony Extract Report'!E2:E1000,'testimony Extract Report'!E2:E1000,0))),MATCH('testimony Extract Report'!E2:E1000,'testimony Extract Report'!E2:E1000,0)),1))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).