Count if less than range

[martinpgibson]

Hi there, I am having real difficulty in figuring this out. The attached document will explain what I mean best but I need 1 formula that will count all instances where a number in row 2 is less than the number above it in row 1. I have highlighted the cell where this instance is true. So the formula should spit out 1 as there is one instance where this true. I want this to be in one formula and not have like a running check in row 3 as I could easily do that. The formula needs to check that the number in row 2 is less than row 1, it can't be done by doing a sum on each row as you will notice in column b the row 2 number is higher than the row 1 number which kind of rules out this method.

As an added bonus if it is possible it would be great to have a formula that actually tells you the difference of when row 2 is lower than row 1. That is, at the moment in the 1 instance where row 2 is less than row 1, it would be good to know how much less that number is, so the answer would be 1 as it would be 1-0=1. But let's say instead of J1 being 1 it was 5, the difference amount would be 4.

Thanks so much for your help in advance!

Count If Less Than Range.xlsx

[MarvinP]

Hi martin,

See if this works for you. It is your problem with two helper rows.

[martinpgibson]

Hi Marvin,

This is fantastic thanks so much for your help. I think I have to settle for this. Do you think it is at all possible to achieve what I want without any helper rows?

[Tony Valko]

If I understand what you're asking try this...

=SUMPRODUCT(--(A2:J2<A1:J1),A1:J1-A2:J2)

[martinpgibson]

Awesome, thanks Tony!

[Teethless mama]

No helper rows, no problem.

=SUMPRODUCT(--(A1:J1>A2:J2))

[Tony Valko]

You're welcome!