building conditional formula
Posted by Jacquie Berkman on February 04, 2002 9:13 AM
I have multiple customers on my WS. A bonus for a
single sales rep is based on when sales is over $25K.
I need a formula to look at each record, and give
a value of 1 for each true amount. Lastly, the total
value of these is multipled by bonus amount. My
current formula works with a single record only.
Posted by Scott on February 04, 2002 12:10 PM
Need more info
What do you mean by a "true amount". Would this be anything over $25K?
Posted by Yogi Anand on February 04, 2002 12:33 PM
Hi Mary:
I don't know if I have got your intent quite right, ... anyway here are two approaches depicted below:
SPerson SAmount QFBonus
Mary 27000 27000 <------ =IF(B12>25000,B12,0)
Richard 23000 0
Jane 25000 0
Mary 19000 0
Bob 31000 31000
Mary 33000 33000
Now Mary's SAmount that would qualify for Bonus
"using
SumIF" Mary 60000 <------ =SUMIF(A12:A17,"Mary",C12:C17)
"using
Array" Mary 60000 <------ {=SUM(IF($A$12:$A$17=B23,$C$12:$C$17))}
HTH
Yogi Anand
ANAND Enterprises (broken link)
Posted by Jacquie on February 05, 2002 12:01 PM
Re: Need more info
Yes - anything over $25K
