SUMIF & VLOOKUP Function

[Mark Sanders]

Dear helpers,

I am having difficulty creating a formula that I believe requires both a SUMIF and VLOOKUP function. I have been looking through other forums and Excel online help for a solution however haven't found one.

My table looks like this:

[TABLE="width: 431"]
<colgroup><col style="width: 85pt; mso-width-source: userset; mso-width-alt: 3616;" width="113"> <col style="width: 66pt;" width="88"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3168;" span="2" width="99"> <col style="width: 66pt;" span="2" width="88"> <tbody>[TR]
[TD="class: xl65, width: 113, bgcolor: transparent"]Row / Column[/TD]
[TD="class: xl65, width: 88, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 99, bgcolor: transparent"]B[/TD]
[TD="class: xl65, width: 99, bgcolor: transparent"]C[/TD]
[TD="class: xl65, width: 88, bgcolor: transparent"]D[/TD]
[TD="class: xl65, width: 88, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]Date[/TD]
[TD="class: xl66, bgcolor: #CCFFCC"]Source[/TD]
[TD="class: xl66, bgcolor: #CCFFCC"]Cost[/TD]
[TD="class: xl65, bgcolor: transparent"]Date[/TD]
[TD="class: xl65, bgcolor: transparent"]Total[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl67, bgcolor: transparent"]4-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"]Phone bill[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 55.00 [/TD]
[TD="class: xl67, bgcolor: transparent"]1-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]3[/TD]
[TD="class: xl67, bgcolor: transparent"]6-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"]Power[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 125.00 [/TD]
[TD="class: xl67, bgcolor: transparent"]2-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl67, bgcolor: transparent"]7-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"]Petrol[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 34.00 [/TD]
[TD="class: xl67, bgcolor: transparent"]3-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl67, bgcolor: transparent"]7-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"]Toms Present[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 20.00 [/TD]
[TD="class: xl67, bgcolor: transparent"]4-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]6[/TD]
[TD="class: xl67, bgcolor: transparent"]14-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"]Lunch & Choc[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 8.50 [/TD]
[TD="class: xl67, bgcolor: transparent"]5-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]7[/TD]
[TD="class: xl67, bgcolor: transparent"]15-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"]Groceries[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 22.00 [/TD]
[TD="class: xl67, bgcolor: transparent"]6-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 8[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]7-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 9[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]8-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 10[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]9-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 11[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]10-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 12[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]11-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 13[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]12-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 14[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]13-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 15[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]14-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 16[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]15-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] 17[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]16-Mar[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

The formula I want to create needs to be entered in cell E2, with the solution being the sum of cost (column C) for the referenced day in the previous column. Eg. the formula looks up D2 amongst the array A2:C17 and provides the total. The 7-Mar referenece (cell E8) shoud equal $54.

I hope this makes sense and would appreciate any advice. If there is any online information a helper can steer me too (or a formula) I will be very thankful.

Best wishes,

Mark Sanders

 

[JoeMo]

In E2 and copy down:
=SUMIF($A$2:$A$7,D2,$C$2:$C$7)

 

[konew1]

=sumproduct(--($a$3:$a$18=d3),($c$3:$c$18))

 

[Mark Sanders]

In E2 and copy down:
=SUMIF($A$2:$A$7,D2,$C$2:$C$7)

Many thanks JoeMo. Much appreciated.