How to count all the items in 2 columns depending on comparison?

Question

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.

Answer 1

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: