VLOOKUP with SUMIF???

[Twinky]

HI,
Can anybody solve this for me?

Three columns. Column A has a list of category codes. Column C is the cost for each, and column E has a drop down list that has a selection that reads "Cost Committed". What I need to do is have a cell that adds the cost in column C but ONLY if the corresponding cell in column E reads "Cost Committed" AND ONLY if the code number for that row item in column A ends with a 2.
I also need another cell that will do the same thing just described but instead of looking only for the codes that end in 2, it would need to add the ones that end in EITHER a 3,4,5 or 6

<table width="436" border="0" cellpadding="0" cellspacing="0"><col style="width: 68pt;" width="91"> <col style="width: 48pt;" width="64" span="3"> <col style="width: 115pt;" width="153"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl153" style="height: 15pt; width: 68pt;" width="91" height="20">A</td> <td class="xl153" style="width: 48pt;" width="64">
</td> <td class="xl153" style="width: 48pt;" width="64">C</td> <td class="xl153" style="width: 48pt;" width="64">
</td> <td class="xl153" style="width: 115pt;" width="153">E</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt;" height="20">01.01.01</td> <td>
</td> <td class="xl152" align="right">300</td> <td>
</td> <td class="xl154">Cost Committed
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.01.02</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">2,300</td> <td>
</td> <td class="xl154" style="border-top: medium none;">Cost Committed
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.01.03</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">200</td> <td>
</td> <td class="xl154" style="border-top: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.01.04</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">350</td> <td>
</td> <td class="xl154" style="border-top: medium none;">Cost Committed
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.01.06</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">800</td> <td>
</td> <td class="xl154" style="border-top: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.01.12</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">3,000</td> <td>
</td> <td class="xl154" style="border-top: medium none;">Cost Committed
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.01.22</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">450</td> <td>
</td> <td class="xl154" style="border-top: medium none;">Cost Committed
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl150" style="height: 15pt; border-top: medium none;" height="20">01.02.00</td> <td>
</td> <td class="xl151">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.02.01</td> <td>
</td> <td class="xl152" align="right">200</td> <td>
</td> <td class="xl154">Cost Committed
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.02.03</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">400</td> <td>
</td> <td class="xl154" style="border-top: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl150" style="height: 15pt; border-top: medium none;" height="20">01.03.00</td> <td>
</td> <td class="xl151">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.03.01</td> <td>
</td> <td class="xl152" align="right">300</td> <td>
</td> <td class="xl154"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.03.02</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">500</td> <td>
</td> <td class="xl154" style="border-top: medium none;">Cost Committed
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.03.03</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">900</td> <td>
</td> <td class="xl154" style="border-top: medium none;">Cost Committed
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.03.04</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">750</td> <td>
</td> <td class="xl154" style="border-top: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.03.05</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">850</td> <td>
</td> <td class="xl154" style="border-top: medium none;">Cost Committed
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl149" style="height: 15pt; border-top: medium none;" height="20">01.03.06</td> <td>
</td> <td class="xl152" style="border-top: medium none;" align="right">350</td> <td>
</td> <td class="xl154" style="border-top: medium none;"> </td> </tr> </tbody></table>

 

[syntaxed]

Maybe:
=SUMPRODUCT(--(RIGHT($A$1:$A$17,1)="2"),--($E$1:$E$17="Cost Committed"),$C$1:$C$17)

And:
=SUMPRODUCT(--(ISNUMBER(MATCH(RIGHT($A$1:$A$17,1),{"3","4","5","6"},0))),--($E$1:$E$17="Cost Committed"),$C$1:$C$17)

 

[Haseeb Avarakkan]

Hello,

If you have Excel 2007 or later,

=SUMIFS(C:C,A:A,"*2",E:E,"Cost Committed")

=SUM(SUMIFS(C:C,A:A,"*"&{3,4,5,6},E:E,"Cost Committed"))

 

[Twinky]

Thank you guys so much! Haseeb in particular since these are the ones I used which worked great. Thanks again for the help!