Countif - comparing multiple cells with others

[Mr Anonymouse]

Hi,

I have a row of cells (E5:BN5) and I want to be able to count the number of
cells in this row that exceed the corresponding value in various other range
of cells, e.g. (E6:BN6).

Similarly, I also want to count the cells if the value of a cell in the
range exceeds the SUM of the value in several ranges. I.E. Countif
(E5:BN5) > SUM (E6:BN6 + E20:BN20).

I hope I've explained this sufficiently? Simply, if 2 cells in the first
range exceed the sum of their counterparts in the corresponding ranges, I
want this to return a value of 2.

Any help gratefully received.

Thanks

 

[Jerry W. Lewis]

=SUMPRODUCT(--(E5:BN5>E6:BN6))
Is the number of cells in E5:BN5 that exceed the corresponding cells in
E6:BN6. (E5:BN5>E6:BN6) is an array of boolean (TRUE/FALSE) values, so
-- is needed to coerce them to numeric (1/0) values that SUMPRODUCT can use.

SUM(E6:BN6+E20:BN20) is a single value, not an array of 62 values (one
for each column in E:BN), so I suspect that you want
=SUMPRODUCT(--(E5:BN5>E6:BN6+E20:BN20))
instead of
=SUMPRODUCT(--(E5:BN5>SUM(E6:BN6+E20:BN20)))

Jerry