0

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.

1

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
1

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.

In your example:

C1 = IF(AND($A1<>"",$A1<$B1),1,0) 

Then your answer would be SUM($C:$C)

  • Thanks, that’s a good solution. With the specific problem I have at the moment, the data is in a sheet linked to SharePoint data so I can’t just add a column beside the data, but I can of course use a different sheet. I’ll need to make sure the formula goes all the way down to accommodate future expansion of the data, and then I’ll use SUMIF to only count the cells with a 1, to avoid the #VALUE! cells that appear when referring to Excel tables rather than row/column references. – user535673 Mar 31 '16 at 9:16
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.