# Count cells based on a comparison with value in the same row of another column

There are many questions about counting cells based on conditions, but I can’t quite find a way to count cells when a value in another column, in the current row, is less than / greater than etc. the current cell. Example:

How many cells in column A (below) have a non-blank value AND that value is less than the value in the same row in column B?

``````A     B
--    --
6
2     1
3*    6
2
4*    6
4     1
``````

The answer should be 2, which is the two I have added as asterisk to (asterisks are not normally in the data).

What I want to use is:

``````=COUNTIFS(A1:A6,"<>",A1:A6,"<"&cell in column B for whatever row Excel is currently looking at)
``````

… but you can’t do that. Any ideas, preferably avoiding VBA?

NB If there is a solution, ideally it will still work when referring to column names in Excel tables. Using things like offsets is prone to future errors.

To avoid having to add counter column, I think you can use a SUMPRODUCT formula:

`=SUMPRODUCT((A1:A6<>"")*(A1:A6<B1:B6))`

• Excellent, thank you! I had tried SUMPRODUCT before but not quite got there. – user535673 Apr 1 '16 at 14:22

Instead of building complex formulas in which you are concatenating comparison symbols, I prefer to create a formula in Column C that will result into 0 or 1. Then simply `SUM` over this column.

``````C1 = IF(AND(\$A1<>"",\$A1<\$B1),1,0)
Then your answer would be `SUM(\$C:\$C)`
• It may be of interest to look into the formula `=IFERROR(value,value_if_error)`. See this link. Glad that you sorted it out. – Sven Mar 31 '16 at 9:19