countif and sumproduct (i think)

[jj_martin_87]

[FONT=&quot]I have an excel sheet with a column of dropdowns for different values. If the value is “apples” I want it to multiply the value by 4%. if there is a cell in the column where apples is chosen, i need it to multiply by 5%. A third 6%. Right now i have two formulas in place

=iferror(if(E21="apples",K21*vlookup($Q$2,$O$2:$P$4,2,false),P21*vlookup(E21,$J$2:$K$14,2,false)),"")

=countif(E21:E37,"apples")

This table represents O1:Q4

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[/FONT]

apples count	rate	current apples count
1	4.00%	1
2	5.00%
3	6.00%

<colgroup><col style="width: 100px"><col width="85"><col width="100"></colgroup><tbody>
</tbody>

[FONT=&quot] [/FONT]

 

[Aladin Akyurek]

In C2 enter and copy down:

=VLOOKUP(COUNTIFS($A$2:$A2,$A2),$E$2:$F$4,2,0)*$B2

If testing for apples is necessary...

In C2 enter and copy down:

=IF($A2="apples",VLOOKUP(COUNTIFS($A$2:A2,A2),$E$2:$F$4,2,0)*$B2,"")

 

[jj_martin_87]

That did it! Thank you very much

 

[Aladin Akyurek]

That did it! Thank you very much

You are welcome.