1

I have two columns with data and I'd like to count all the rows where the value in the first column are greater than the values in the second column.

The best I could find was using cell references, something like this:

=COUNTIF($D$2:$D$289, ">"&$E$2)

but obviously it is not what I need.

However, I did try the following:

=COUNTIF($D$2:$D$289, ">"&$E$2:$E$289)

but I get an error...

Can you advise please?

Thanks.

enter image description here

Improve this question

1 Answer 1

Reset to default
2

In order to compare the ranges the way you want, you have to use a formula that will give you a cell-by-cell comparison. For this solution, use SUMPRODUCT.

For Excel:

=SUMPRODUCT(--($D$2:$D$289>&$E$2:$E$289))

How it works is that the inner comparison $D$2:$D$289>&$E$2:$E$289 will generate an array of TRUE/FALSE values. The -- part of the formula converts all the TRUEs and FALSEs into a array of 1 and 0. Finally the SUMPRODUCT simply sums up all the ones in the array.

For Google Sheets it's simply:

==SUMPRODUCT($D$2:$D$289>&$E$2:$E$289)

Results given the example data from OP:

enter image description here

Improve this answer
7
  • Thank you Peter. I get an "Error parsing formula" error message. Maybe that's because I work on Google Spreadsheets??
    – dushkin
    Commented Jul 20, 2018 at 15:31
  • @dushkin then you should not tag with Excel.
    – Scott Craner
    Commented Jul 20, 2018 at 16:15
  • @duskin, see update answer
    – PeterT
    Commented Jul 20, 2018 at 16:36
  • @ScottCraner Scott, since there is much common between the two, I was hoping the excel guys could help me as well.
    – dushkin
    Commented Jul 20, 2018 at 16:44
  • @PeterT I made the change. Still Error (as you may see in the question image I've uploaded)... I also tried it on a separate other very tiny range of two columns - and still error... :(
    – dushkin
    Commented Jul 20, 2018 at 16:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .