What kind of data do you have in column C?
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 by dhsilv2; Sep 9th, 2014 at 02:27 PM.
What kind of data do you have in column C?
Assuming too much and qualifying too much are two faces of the same problem.
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:
where B2 is a number.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)))))
2. Again CSE:
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)))))
Assuming too much and qualifying too much are two faces of the same problem.
Like this thread? Share it with others