Another difficult failed formula

[Ken M.]

=SUMIF(Q2="John","+",E2<=1000,E2*5%,N2+R2+S2)

In this formula, I am tying to get the cell to do the following...

If sales rep name Q2 is John and the sale is greater than $1000 we should
take the total sale amount E2 and multiply by 5% then add it to the N2+R2+S2
cells to total commission due.

I am still trying to muddle through this. Logic failing me at midnight.

Thanks again for any help.

 

[JulieD]

Hi Ken

SUMIF can't take multiple criteria and for a single cell you don't need
SUMIF anyway ...
just use an IF statement
=IF(AND(Q2="John",E2<=1000),E2*5%+N2+R2+S2,0)

Cheers
JulieD

 

[Arvi Laanemets]

Hi

=IF(AND(Q2="John",E2>1000),0.05*E2+SUM(N2,R2:S2),"whatever")


Arvi Laanemets