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]

 

[jj_martin_87]

i meant to say "if there is a second cell in the column where apples is chosen, i need it to multiply by 5%"

 

[Guru008]

Hi,

Try this
=C1*INDEX(E1:F3,MATCH(COUNTIF(A1:A2,"apple"),E1:E3,0),2)

change range as per your data

 

[jj_martin_87]

That works to multiply by 4%, but doesnt increase based on the number of times apples is mentioned.

this might make it clearer

	paid	total		apples count	rate	current count
apples	$100.00	$5.00		1	4.00%	2
apples	$100.00	$5.00		2	5.00%
bananas	$100.00	$0.00		3	6.00%

<colgroup><col style="width: 100px;"><col width="100"><col width="114"><col width="100"><col width="106"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

I would like the first row with apple to still be multiplied by 4% the second by 5% third by 6%
Formulas are:
=countif(G59:G61,"apples")
=iferror(if(G59="apples",H59*vlookup($M$59,$K$59:$L$61,2,false),P21*vlookup(G59,$J$2:$K$14,2,false)),"")

 

[Aladin Akyurek]

What must happen if you have 10 records of apples in the range of interest?

 

[jj_martin_87]

I only need it to go to three apples (6%)

 

[Aladin Akyurek]

I only need it to go to three apples (6%)

What would be the expected results given the samples of A:G?

 

[jj_martin_87]

in the scenario above, (apples chosen twice) it would be $9. (first apple at 4%=$4 second apple at 5% = $5). Right now, if the apples count is two, it multiplies both by 5% based on the vlookup.

 

[jj_martin_87]

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

	paid	total		apples count	rate	current count
apples	100	$4.00		1	4.00%	1
bananas	100			2	5.00%
oranges	100			3	6.00%

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

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

	paid	total		apples count	rate	current count
apples	100	$5.00		1	4.00%	2
apples	100	$5.00		2	5.00%
oranges	100			3	6.00%

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

 

[jj_martin_87]

=iferror(if(A2="apples",B2*vlookup($G$2,$E$2:$G$4,2,false),E2*vlookup(E1,$J$2:$K$14,2,false)),"")
=countif(A2:A4,"apples")