Countif range less than range

[milo1984]

Hi,

Probably the simplest question which I just cannot figure out.

I have a range of percentages. Say, B2:B10 and I want to Countif B2:B10 is less than Range A2:A10. (Column A is targets, B is actuals).

I would really appreciate your help with this.

Many thanks

[bebo021999]

Maybe:
=SUMPRODUCT(--(B2:B10-A2:A10<0))

[milo1984]

Thank you for this. Can I do a sumproduct based on multiple cells? ie B2,B9,B15-A2,A9,A15>0? I just get a #Value! error.

thanks in advance

[daddylonglegs]

Is there another column in your data you could use to determine which rows to compare?

[milo1984]

my problem is in one column, I have a mixture of percentages and numbers. These are targets vs actuals. So, the formula is counting if the actual percentage is lower than the target, but I also need to include if the actual number is greater than the target (ie you were allowed two fails, but had 3)

This is a set format so cannot be changed. I am just arranging a summary sheet to draw the data in from multiple excels.

Thanks

[bebo021999]

Do row 2,9,15, which you like to work with, have any thing different to others, in order to mark them? For instant, C2,C9,C15 contain text "Average", or some thing else. Then we can mark these row to use SUMPRODUCT.
Value as average normally around 1, right? Is it a big difference to other values in the column?
Can you upload a small worksheet?

[milo1984]

yes, they are all marked with "SEE" in column A.

I have uploaded a test workbook. I will just have a summary based on the total number of fails (ie under target % and over target number ("SEE")

Many thanks in advance

[bebo021999]

So with "SEE" in A column, find the difference btw the two coresponding columns C & B, then count where C < B?

If yes, try:

=SUMPRODUCT(--(IF($A$1:$A$23="SEE",($C$1:$C$23-$B$1:$B$23),0)<0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces

{ } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[milo1984]

Thank you, that is great. Can I do the same but if it does not equal "SEE"? as in my test file, I need to know the number of fails so this would be everything under target percentage, plus over the target "SEE".

If I do the same formula for all, it counts all the "SEE" as a fail because it is less than the target (not so much a target, but an allowed number of fails).

I really appreciate this help.

Thanks in advance