Sum Product Question - Count if multiple columns are not equal

[dhsilv2]

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?

 

[Aladin Akyurek]

What kind of data do you have in column C?

 

[dhsilv2]

What kind of data do you have in column C?

C was 0-4 and I wanted to not count 0's. The issue was with column B which was a year stored as '2010 instead of 2010. I started with this as a countifs but the requirements became such I couldn't use the ifs statement. I rarely use sumproduct and I guess it's more sensitive to numbers stored as text? And I wasn't able to turn the countifs into an array of column C does or does not equal A. I'd much rather do a countifs for countless reasons.

 

[Aladin Akyurek]

C was 0-4 and I wanted to not count 0's. The issue was with column B which was a year stored as '2010 instead of 2010. I started with this as a countifs but the requirements became such I couldn't use the ifs statement. I rarely use sumproduct and I guess it's more sensitive to numbers stored as text? And I wasn't able to turn the countifs into an array of column C does or does not equal A. I'd much rather do a countifs for countless reasons.

1. Control+shift+enter (CSE), not just enter:

=SUM(IF(Sheet2!$C$2:$C$1000>0,IF(Sheet2!$F$:$F$1000=A4,
  IF(Sheet2!$B$2:$B$1000+0=B$2,IF(Sheet2!$C$2:$C$1000=Sheet2!$E$2:$E$1000,1)))))

where B2 is a number.

2. Again CSE:

=SUM(IF(Sheet2!$C$2:$C$1000>0,IF(Sheet2!$F$:$F$1000=A4,
  IF(Sheet2!$B$2:$B$1000+0=B$2,IF(1-(Sheet2!$C$2:$C$1000=Sheet2!$E$2:$E$1000),1)))))