I've got a large sheet of data with multiple columns. Need to count the number of items that meet certain criteria. For example I wanted to see if the two sets of data met 2 criteria and if so to count them.

{=SUMPRODUCT(IF(Sheet2!C:C>0,IF(Sheet2!F:F=A4,IF(Sheet2!B:B=B$2,--(Sheet2!C:C=Sheet2!E:E)))))}

This works perfectly.

Now I want to do the exact same count only I want to know when sheet2!C:C <> Sheet2!E:E

When I change the formula to this

{=SUMPRODUCT(IF(Sheet2!C:C>0,IF(Sheet2!F:F=A4,IF(Sheet2!B:B=B$2,--(Sheet2!C:C<>Sheet2!E:E)))))}

I am getting 0 even though I know there are 34 items that should show up in this calculation.

Yes I'm aware that doing the array for the whole column is slowing down the calculation, I've just accepted it for this so I can add data if needed.

This appears to work. Anyone able to help why a sumifs is fine with numbers as text but sumproduct cannot identify that text 2010= number 2010?

{=SUMPRODUCT(IF(Sheet2!C:C>0,IF(Sheet2!F:F=A4,IF(Sheet2!B:B=B$2,--(Sheet2!C:C=Sheet2!E:E)))))}

This works perfectly.

Now I want to do the exact same count only I want to know when sheet2!C:C <> Sheet2!E:E

When I change the formula to this

{=SUMPRODUCT(IF(Sheet2!C:C>0,IF(Sheet2!F:F=A4,IF(Sheet2!B:B=B$2,--(Sheet2!C:C<>Sheet2!E:E)))))}

I am getting 0 even though I know there are 34 items that should show up in this calculation.

Yes I'm aware that doing the array for the whole column is slowing down the calculation, I've just accepted it for this so I can add data if needed.

This appears to work. Anyone able to help why a sumifs is fine with numbers as text but sumproduct cannot identify that text 2010= number 2010?

Last edited: