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

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 | |
  • Thank you Peter. I get an "Error parsing formula" error message. Maybe that's because I work on Google Spreadsheets?? – dushkin Jul 20 '18 at 15:31
  • @dushkin then you should not tag with Excel. – Scott Craner Jul 20 '18 at 16:15
  • @duskin, see update answer – PeterT Jul 20 '18 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 Jul 20 '18 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 Jul 20 '18 at 16:47

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.