SUMIF - - but I need more ...

[dcavaiani]

[TABLE="width: 280"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Calc[/TD]
[TD]Type[/TD]
[TD]ExpRatio[/TD]
[TD]Holding%[/TD]
[TD]FundCost[/TD]
[TD]Mixed[/TD]
[/TR]
[TR]
[TD="align: right"]$36,233[/TD]
[TD]equity[/TD]
[TD="align: right"]0.69%[/TD]
[TD="align: right"]9.35%[/TD]
[TD="align: right"]$250[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$29,026[/TD]
[TD]mixed[/TD]
[TD="align: right"]0.60%[/TD]
[TD="align: right"]7.49%[/TD]
[TD="align: right"]$174[/TD]
[TD="align: right"]0.38[/TD]
[/TR]
[TR]
[TD="align: right"]$41,933[/TD]
[TD]fixed[/TD]
[TD="align: right"]0.97%[/TD]
[TD="align: right"]10.82%[/TD]
[TD="align: right"]$407[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$32,045[/TD]
[TD]equity[/TD]
[TD="align: right"]0.77%[/TD]
[TD="align: right"]8.27%[/TD]
[TD="align: right"]$247[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$9,934[/TD]
[TD]mixed[/TD]
[TD="align: right"]0.63%[/TD]
[TD="align: right"]2.56%[/TD]
[TD="align: right"]$63[/TD]
[TD="align: right"]0.51[/TD]
[/TR]
[TR]
[TD="align: right"]$8,761[/TD]
[TD]equity[/TD]
[TD="align: right"]1.31%[/TD]
[TD="align: right"]2.26%[/TD]
[TD="align: right"]$115[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$7,429[/TD]
[TD]mixed[/TD]
[TD="align: right"]1.04%[/TD]
[TD="align: right"]1.92%[/TD]
[TD="align: right"]$77[/TD]
[TD="align: right"]0.29[/TD]
[/TR]
[TR]
[TD="align: right"]$42,075[/TD]
[TD]equity[/TD]
[TD="align: right"]0.90%[/TD]
[TD="align: right"]10.86%[/TD]
[TD="align: right"]$379[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$19,146[/TD]
[TD]equity[/TD]
[TD="align: right"]1.13%[/TD]
[TD="align: right"]4.94%[/TD]
[TD="align: right"]$216[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$22,023[/TD]
[TD]equity[/TD]
[TD="align: right"]0.96%[/TD]
[TD="align: right"]5.68%[/TD]
[TD="align: right"]$211[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$14,574[/TD]
[TD]fixed[/TD]
[TD="align: right"]0.93%[/TD]
[TD="align: right"]3.76%[/TD]
[TD="align: right"]$136[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$28,024[/TD]
[TD]equity[/TD]
[TD="align: right"]0.62%[/TD]
[TD="align: right"]7.23%[/TD]
[TD="align: right"]$174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$24,800[/TD]
[TD]mixed[/TD]
[TD="align: right"]0.56%[/TD]
[TD="align: right"]6.40%[/TD]
[TD="align: right"]$139[/TD]
[TD="align: right"]0.24[/TD]
[/TR]
[TR]
[TD="align: right"]$12,119[/TD]
[TD]fixed[/TD]
[TD="align: right"]0.62%[/TD]
[TD="align: right"]3.13%[/TD]
[TD="align: right"]$75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$36,001[/TD]
[TD]equity[/TD]
[TD="align: right"]0.75%[/TD]
[TD="align: right"]9.29%[/TD]
[TD="align: right"]$270[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$9,527[/TD]
[TD]equity[/TD]
[TD="align: right"]1.07%[/TD]
[TD="align: right"]2.46%[/TD]
[TD="align: right"]$102[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$0[/TD]
[TD]fixed[/TD]
[TD="align: right"]0.99%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]$0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$13,764[/TD]
[TD]equity[/TD]
[TD="align: right"]1.38%[/TD]
[TD="align: right"]3.55%[/TD]
[TD="align: right"]$190[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Given the above data, I want to ADD UP (and place in a new cell): IF "Type" = "mixed", Then I want to multiply the ratio in the "mixed" column Times the amount in the Calc column.

 

[gaz_chops]

Try

=SUMPRODUCT(($B$3:$B$21="mixed")*($A$3:$A$21)*($F$3:$F$21))

 

[AlanY]

is that what you're after?

Book1
	A	B	C	D	E	F	G
1	Calc	Type	ExpRatio	Holding%	FundCost	Mixed
2	$36,233	equity	0.69%	9.35%	250		$24,203
3	$29,026	mixed	0.60%	7.49%	174	0.38
4	$41,933	fixed	0.97%	10.82%	407
5	$32,045	equity	0.77%	8.27%	247
6	$9,934	mixed	0.63%	2.56%	63	0.51
7	$8,761	equity	1.31%	2.26%	115
8	$7,429	mixed	1.04%	1.92%	77	0.29
9	$42,075	equity	0.90%	10.86%	379
10	$19,146	equity	1.13%	4.94%	216
11	$22,023	equity	0.96%	5.68%	211
12	$14,574	fixed	0.93%	3.76%	136
13	$28,024	equity	0.62%	7.23%	174
14	$24,800	mixed	0.56%	6.40%	139	0.24
15	$12,119	fixed	0.62%	3.13%	75
16	$36,001	equity	0.75%	9.29%	270
17	$9,527	equity	1.07%	2.46%	102
18	$0	fixed	0.99%	0.00%	0
19	$13,764	equity	1.38%	3.55%	190
Sheet1

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(A2:A19,F2:F19)

 

[dcavaiani]

Try

=SUMPRODUCT(($B$3:$B$21="mixed")*($A$3:$A$21)*($F$3:$F$21))

Awesome, thank you !

 

[dcavaiani]

is that what you're after?

	A	B	C	D	E	F	G
Calc	Type	ExpRatio	Holding%	FundCost	Mixed
equity
mixed
fixed
equity
mixed
equity
mixed
equity
equity
equity
fixed
equity
mixed
fixed
equity
equity
fixed
equity

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]$36,233[/TD]

[TD="align: right"]0.69%[/TD]
[TD="align: right"]9.35%[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]$24,203[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]$29,026[/TD]

[TD="align: right"]0.60%[/TD]
[TD="align: right"]7.49%[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]0.38[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]$41,933[/TD]

[TD="align: right"]0.97%[/TD]
[TD="align: right"]10.82%[/TD]
[TD="align: right"]407[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]$32,045[/TD]

[TD="align: right"]0.77%[/TD]
[TD="align: right"]8.27%[/TD]
[TD="align: right"]247[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]$9,934[/TD]

[TD="align: right"]0.63%[/TD]
[TD="align: right"]2.56%[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]0.51[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]$8,761[/TD]

[TD="align: right"]1.31%[/TD]
[TD="align: right"]2.26%[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]$7,429[/TD]

[TD="align: right"]1.04%[/TD]
[TD="align: right"]1.92%[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]0.29[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]$42,075[/TD]

[TD="align: right"]0.90%[/TD]
[TD="align: right"]10.86%[/TD]
[TD="align: right"]379[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]$19,146[/TD]

[TD="align: right"]1.13%[/TD]
[TD="align: right"]4.94%[/TD]
[TD="align: right"]216[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]$22,023[/TD]

[TD="align: right"]0.96%[/TD]
[TD="align: right"]5.68%[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]$14,574[/TD]

[TD="align: right"]0.93%[/TD]
[TD="align: right"]3.76%[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]$28,024[/TD]

[TD="align: right"]0.62%[/TD]
[TD="align: right"]7.23%[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]$24,800[/TD]

[TD="align: right"]0.56%[/TD]
[TD="align: right"]6.40%[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]0.24[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]$12,119[/TD]

[TD="align: right"]0.62%[/TD]
[TD="align: right"]3.13%[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]$36,001[/TD]

[TD="align: right"]0.75%[/TD]
[TD="align: right"]9.29%[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]$9,527[/TD]

[TD="align: right"]1.07%[/TD]
[TD="align: right"]2.46%[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]$0[/TD]

[TD="align: right"]0.99%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]$13,764[/TD]

[TD="align: right"]1.38%[/TD]
[TD="align: right"]3.55%[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=SUMPRODUCT(A2:A19,F2:F19)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Awesome - you have actually simplified it too. Sometimes we just think too hard