Excel SUM and IF combine help

Question

I have two columns of numbers. Both are 1 to 5. I want to count all the cells where the left column value equals the right column value AND the left column value equals a certain value.

I tried this:

=SUM(IF(W2:W13=X2:X13 AND W2:W13=4,1,0))

I've tried pressing Ctrl+Shift+Enter and it adds {} around the formula but that didn't help either.

I think it's the W2:W13 = 4 part that doesn't work

Answer 1

Have you considered a third column (C) with the formula IF(A1=B1,1,0) and then summing that third column?

Answer 2

=COUNTIFS(W2:W13,"=4", X2:X13, "=4")

Answer 3

I'm not much of an Excel Expert, but didn't they craeted the COUNTIF(range, criteria) function for this?

Answer 4

You can use the sumif() function:

SumIf( range, criteria, sum_range )

it will apply the criteria for each row in the range.

Edit: to count the matches, you can use sum_range = 1 or use the Countif() function suggested by Ben in his answer

Answer 5

Add a third column eg Z2:Z13 with this formula: IF(AND(W2=X2; W2=4); 1; 0) Then sum that one.

Answer 6

I don't have Excel 2007. So here's how you can do it in Excel 2003:

=COUNT(IF((W2:W14=4)*(X2:X14=4),Y2:Y14))

Since you are looking for a specific value and the column next to it to be the same value, you can just compare both columns to the same value.

The trick to get this to work is after entering the formula you need to hit F2 to go into edit mode and then hit CTRL-SHIFT-ENTER which makes this formula an array formula. This will put {} around the entire formula. Without making this an array formula this formula won't work.

I found this information in the Excel help document titled Count how often a value occurs