MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

SumIF" Mary 60000 <------ =SUMIF(A12:A17,"Mary",C12:C17)

Array" Mary 60000 <------ {=SUM(IF($A$12:$A$17=B23,$C$12:$C$17))}


Yogi Anand
ANAND Enterprises (broken link)

Posted by Jacquie on February 05, 2002 12:01 PM

Re: Need more info

Yes - anything over $25K