Return Sum of Multiple VLOOKUP results?

privxls · Sep 12, 2019

Good day everyone,

Yet again I have returned with an inquiry. I am trying to work on this sales file where I wanted to extract the daily sales.

Here's the screenshot:
(Screenshot below is just a part of a big raw file which contains over 6 months worth of sales data)

Now, I was able to count the number of days by manually typing Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday beside column B however I just cannot figure out for the life of me how to VLOOKUP and return the sum of each day.

I tried performing SUMIF and SUMPRODUCT but to no avail. I initially thought of performing multiple VLOOKUP however it would always return the same line.

Would any of you possibly know how I can do a lookup in column B with criteria per se 'Monday' and what it would do is count the number of 'Mondays' in column B and return the sum of those Mondays which can be found in column D.

Toadstool · Sep 12, 2019

Hi privxls,

You can use TEXT to get the day of the week into the first column, Then COUNTIF and SUMIFS to total by day.

	A	B	C	D	E	F	G	H	I

Monday
Thursday	Tuesday
Friday	Wednesday
Saturday	Thursday
Sunday	Friday
Monday	Saturday
Tuesday	Sunday
Wednesday
Thursday
Friday
Saturday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] "]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: center"]Sales[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] "]Day of the Week[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: center"]Total # Items[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: center"]Total Count[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: center"]Total Sales[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: center"]No. Items[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFD966]#FFD966[/URL] , align: center"]Sales[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$3,442.18[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]Thursday, August 1, 2019[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]$8,231.30[/TD]
[TD="align: right"][/TD]

[TD="align: center"]60[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$8,979.60[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]Friday, August 2, 2019[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]$3,442.18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$6,734.70[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]Saturday, August 3, 2019[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]$4,340.14[/TD]
[TD="align: right"][/TD]

[TD="align: center"]84[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$12,571.44[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]Sunday, August 4, 2019[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$1,496.60[/TD]
[TD="align: right"][/TD]

[TD="align: center"]53[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$7,931.98[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]Monday, August 5, 2019[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]$3,442.18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$5,298.14[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]Tuesday, August 6, 2019[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]$8,979.60[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$1,496.60[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]Wednesday, August 7, 2019[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]$6,734.70[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Total[/TD]
[TD="align: center"]316[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$46,454.64[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]Thursday, August 8, 2019[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]$4,340.14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]Friday, August 9, 2019[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]$4,489.80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]Saturday, August 10, 2019[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]$958.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

privxls

[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] "]G5[/TH]
[TD="align: left"]=SUMIFS(C$6:C$15,$A$6:$A$15,$F5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=COUNTIF($A$6:$A$15,F5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=SUMIFS(D$6:D$15,$A$6:$A$15,$F5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G12
[/TH]
[TD="align: left"]=SUM(G5:G11)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H12[/TH]
[TD="align: left"]=SUM(H5:H11)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I12[/TH]
[TD="align: left"]=SUM(I5:I11)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A6
[/TH]
[TD="align: left"]=TEXT(B6,"dddd")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

kweaver · Sep 12, 2019

You can also use WEEKDAY such as:

Code:

=SUMPRODUCT((WEEKDAY($B$6:$B$18,2)=ROW()-5)*$D$6:$D$18)

Excel 2010

A

B

C

D

E

F

G

6

Don't need

Thursday, August 01, 2019

$6,857

Monday

$14,906

7

Friday, August 02, 2019

$7,656

Tuesday

$10,234

8

Saturday, August 03, 2019

$9,985

Wednesday

$9,703

9

Sunday, August 04, 2019

$8,137

Thursday

$9,962

10

Monday, August 05, 2019

$5,770

Friday

$14,513

11

Tuesday, August 06, 2019

$5,807

Saturday

$14,375

12

Wednesday, August 07, 2019

$9,703

Sunday

$17,260

13

Thursday, August 08, 2019

$3,105

14

Friday, August 09, 2019

$6,857

15

Saturday, August 10, 2019

$4,390

16

Sunday, August 11, 2019

$9,123

17

Monday, August 12, 2019

$9,136

18

Tuesday, August 13, 2019

$4,427

Sheet28

Cell Formulas
Range	Formula
G6	=SUMPRODUCT((WEEKDAY($B$6:$B$18,2)=ROW()-5)*$D$6:$D$18)

privxls · Sep 12, 2019

Toadstool said:
Hi privxls,

You can use TEXT to get the day of the week into the first column, Then COUNTIF and SUMIFS to total by day.

A B C D E F G H I
Date Day of the Week
Monday
Thursday Tuesday
Friday Wednesday
Saturday Thursday
Sunday Friday
Monday Saturday
Tuesday Sunday
Wednesday
Thursday
Friday
Saturday

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

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

[TD="align: center"]Total # Items[/TD]
[TD="align: center"]Total Count[/TD]
[TD="align: center"]Total Sales[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]No. Items[/TD]
[TD="align: center"]Sales[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$3,442.18[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]Thursday, August 1, 2019[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]$8,231.30[/TD]
[TD="align: right"][/TD]

[TD="align: center"]60[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$8,979.60[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]Friday, August 2, 2019[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]$3,442.18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$6,734.70[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]Saturday, August 3, 2019[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]$4,340.14[/TD]
[TD="align: right"][/TD]

[TD="align: center"]84[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$12,571.44[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]Sunday, August 4, 2019[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$1,496.60[/TD]
[TD="align: right"][/TD]

[TD="align: center"]53[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$7,931.98[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]Monday, August 5, 2019[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]$3,442.18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$5,298.14[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]Tuesday, August 6, 2019[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]$8,979.60[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$1,496.60[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]Wednesday, August 7, 2019[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]$6,734.70[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Total[/TD]
[TD="align: center"]316[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$46,454.64[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]Thursday, August 8, 2019[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]$4,340.14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]Friday, August 9, 2019[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]$4,489.80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]Saturday, August 10, 2019[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]$958.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
privxls

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G5[/TH]
[TD="align: left"]=SUMIFS(C$6:C$15,$A$6:$A$15,$F5)[/TD]
[/TR]
[TR]
[TH]H5[/TH]
[TD="align: left"]=COUNTIF($A$6:$A$15,F5)[/TD]
[/TR]
[TR]
[TH]I5[/TH]
[TD="align: left"]=SUMIFS(D$6:D$15,$A$6:$A$15,$F5)[/TD]
[/TR]
[TR]
[TH]G12[/TH]
[TD="align: left"]=SUM(G5:G11)[/TD]
[/TR]
[TR]
[TH]H12[/TH]
[TD="align: left"]=SUM(H5:H11)[/TD]
[/TR]
[TR]
[TH]I12[/TH]
[TD="align: left"]=SUM(I5:I11)[/TD]
[/TR]
[TR]
[TH]A6[/TH]
[TD="align: left"]=TEXT(B6,"dddd")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Toadstool,

Thank you so much for doing this. Even a five year old can certainly understand your explanation as you have narrowed it down and the response is very concise. I appreciate your time and effort and I would love to let you know that this formula worked as expected.

Thank you so much.

privxls · Sep 12, 2019

kweaver said:
You can also use WEEKDAY such as:

Code:

=SUMPRODUCT((WEEKDAY($B$6:$B$18,2)=ROW()-5)*$D$6:$D$18)

Excel 2010

A B C D E F G
Don't need Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

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

[TD="align: right"]Thursday, August 01, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,857[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$14,906[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Friday, August 02, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$7,656[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$10,234[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Saturday, August 03, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$9,985[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$9,703[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Sunday, August 04, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$8,137[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$9,962[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Monday, August 05, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$5,770[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$14,513[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Tuesday, August 06, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$5,807[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$14,375[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Wednesday, August 07, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$9,703[/TD]
[TD="align: right"][/TD]

[TD="align: right"]$17,260[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Thursday, August 08, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$3,105[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Friday, August 09, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,857[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Saturday, August 10, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$4,390[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Sunday, August 11, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$9,123[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Monday, August 12, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$9,136[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Tuesday, August 13, 2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$4,427[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet28

[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] "]G6[/TH]
[TD="align: left"]=SUMPRODUCT((WEEKDAY($B$6:$B$18,2)=ROW()-5)*$D$6:$D$18)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your response and effort Kweaver, I certainly appreciate people around here in MrExcel Forums. You guys do an amazing job and solution for every question.

I have tried this and at first, I seem to have a difficulty understanding the formula but I spent time and tried to understand how the formula works, I now get it

Thanks everyone.

--

This thread / inquiry is now considered as resolved.

kweaver · Sep 12, 2019

Great. Thanks for the feedback!

PaddyD · Sep 12, 2019

Or just add weekday as an extra column to the data (using text() as described above) and do all the rest in a pivot table - take you about 10 clicks all up

Return Sum of Multiple VLOOKUP results?

privxls

Board Regular

Excel Facts

Toadstool

Well-known Member

kweaver

Well-known Member

privxls

Board Regular

privxls

Board Regular

kweaver

Well-known Member

PaddyD

MrExcel MVP

Similar threads

Forum statistics

Share this page

	A	B	C	D	E	F	G	H	I
Date	Day of the Week
Monday
Thursday	Tuesday
Friday	Wednesday
Saturday	Thursday
Sunday	Friday
Monday	Saturday
Tuesday	Sunday
Wednesday
Thursday
Friday
Saturday

Return Sum of Multiple VLOOKUP results?

privxls

Board Regular

Excel Facts

Toadstool

Well-known Member

kweaver

Well-known Member

privxls

Board Regular

privxls

Board Regular

kweaver

Well-known Member

PaddyD

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock