Count and many conditions

Hi

I am trying to interrogate a spreadsheet to look down two columns comparing only Cells that have P5 in the C column and if column Y is greater than column L - as can be seen below)

SUMPRODUCT(--(SCHOOL!$C:$C="P5"), --(SCHOOL!Y:YgreaterthanSCHOOL!L:L))

This produces the number of cells that this applies to but I want to factor in a BUFFER.

E.g. If the Cell checked in column Y is 5% above that of Column L then count it.

How do I add this to the formula?

Nb words used instead of symbol in formula, purely due to filters on this website

Hi

=SUMPRODUCT(--(SCHOOL!$C:$C="P5"), --(SCHOOL!Y:Y>SCHOOL!L:L)).

is what I have except it doesn't say to only include anything where Y:Y is 5% or more larger than L:L

I think that brought #value but will try again tomorrow at work.

As suspected #value is the cell response

Try

=SUMPRODUCT(--(SCHOOL!$C2:$C100="P5"),--(SCHOOL!Y2:Y100>(SCHOOL!L2:L100*1.05)))

Originally Posted by JohnTopley

Try

=SUMPRODUCT(--(SCHOOL!$C2:$C100="P5"),--(SCHOOL!Y2:Y100>(SCHOOL!L2:L100*1.05)))

Nope still getting #value

Value is usually because of wrong data type e.g text not numeric, not the formula.

Post a sample Excel file which has the problem.