Saturday, June 22, 2019

microsoft excel - 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.


Answer



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


=SUMPRODUCT((A1:A6<>"")*(A1:A6


-

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...