Vlookup to bring back multiply values

[Mldeuser]

I am trying to write a formula that look at the header on the main tab for example AC6 contains a date 11/01/2020. I want it to look at the date in this tab, then go to tab titled Care Design and look the dates in column C and when it finds a match bring back the value in column I. The problem there may be more than one row where the date matches. I need the formula to bring the total of the two rows.

For example rows 48 & 49 both have the dates 11/01/2020 in column C. The value in row 48 column I is 44,195.67 and the value in row 49 column I is 6,799.33 for a total value of $50,995 this is the value I need brought back to the main tab.

This is the formula I am currently using, it stops at the first value it finds.

=VLOOKUP(AC$6,'Care Design'!$C$22:$K$300,5,FALSE).

Thank you

 

[Toadstool]

Hi Mldeuser,

A little SUMIFS should do it.

Here's my Care Designs tab:

	C	D	I
Value

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]47[/TD]
[TD="align: center"]10/30/2020[/TD]
[TD="align: right"][/TD]
[TD="align: right"]22.00[/TD]

[TD="align: center"]48[/TD]
[TD="align: center"]11/1/2020[/TD]
[TD="align: right"][/TD]
[TD="align: right"]44,195.67[/TD]

[TD="align: center"]49[/TD]
[TD="align: center"]11/1/2020[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6,799.33[/TD]

[TD="align: center"]50[/TD]
[TD="align: center"]11/2/2020[/TD]
[TD="align: right"][/TD]
[TD="align: right"]22.00[/TD]

[TD="align: center"]51[/TD]
[TD="align: center"]11/3/2020[/TD]
[TD="align: right"][/TD]
[TD="align: right"]22.00[/TD]

</tbody>

Care Design​

Here's the date in column AC and the SUMIFS in column AD

	AC	AD

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

[TD="align: center"]6[/TD]
[TD="align: center"]11/1/2020[/TD]
[TD="align: center"]$50,995.00[/TD]

</tbody>

Main​

[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] "]AD6[/TH]
[TD="align: left"]=SUMIFS('Care Design'!I:I,'Care Design'!C:C,Main!AC6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mldeuser]

Thank you worked perfectly