# Sum Product Question - Count if multiple columns are not equal

#### dhsilv2

##### Board Regular
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?

Last edited:

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
What kind of data do you have in column C?

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.

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:
Rich (BB code):
``````=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:
Rich (BB code):
``````=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)))))
``````

Replies
3
Views
106
Replies
1
Views
128
Replies
7
Views
592
Replies
3
Views
496
Replies
3
Views
677

1,203,350
Messages
6,054,906
Members
444,759
Latest member
TeckTeck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back