Return Sum of Multiple VLOOKUP results?

privxls

Board Regular
Joined
Nov 22, 2016
Messages
55
Office Version
  1. 2021
Platform
  1. Windows
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)
mckNXqj.png


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.

:confused::confused::confused:
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.

ABCDEFGHI
4DateSalesDay of the WeekTotal # ItemsTotal CountTotal Sales
5No. ItemsSalesMonday231$3,442.18
6ThursdayThursday, August 1, 201955$8,231.30Tuesday601$8,979.60
7FridayFriday, August 2, 201923$3,442.18Wednesday451$6,734.70
8SaturdaySaturday, August 3, 201929$4,340.14Thursday842$12,571.44
9SundaySunday, August 4, 201910$1,496.60Friday532$7,931.98
10MondayMonday, August 5, 201923$3,442.18Saturday412$5,298.14
11TuesdayTuesday, August 6, 201960$8,979.60Sunday101$1,496.60
12WednesdayWednesday, August 7, 201945$6,734.70Total31610$46,454.64
13ThursdayThursday, August 8, 201929$4,340.14
14FridayFriday, August 9, 201930$4,489.80
15SaturdaySaturday, August 10, 201912$958.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
privxls

Worksheet Formulas
CellFormula
G5=SUMIFS(C$6:C$15,$A$6:$A$15,$F5)
H5=COUNTIF($A$6:$A$15,F5)
I5=SUMIFS(D$6:D$15,$A$6:$A$15,$F5)
G12
=SUM(G5:G11)
H12=SUM(H5:H11)
I12=SUM(I5:I11)
A6
=TEXT(B6,"dddd")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
You can also use WEEKDAY such as:

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


Excel 2010
ABCDEFG
6Don't needThursday, August 01, 2019$6,857Monday$14,906
7Friday, August 02, 2019$7,656Tuesday$10,234
8Saturday, August 03, 2019$9,985Wednesday$9,703
9Sunday, August 04, 2019$8,137Thursday$9,962
10Monday, August 05, 2019$5,770Friday$14,513
11Tuesday, August 06, 2019$5,807Saturday$14,375
12Wednesday, August 07, 2019$9,703Sunday$17,260
13Thursday, August 08, 2019$3,105
14Friday, August 09, 2019$6,857
15Saturday, August 10, 2019$4,390
16Sunday, August 11, 2019$9,123
17Monday, August 12, 2019$9,136
18Tuesday, August 13, 2019$4,427
Sheet28
Cell Formulas
RangeFormula
G6=SUMPRODUCT((WEEKDAY($B$6:$B$18,2)=ROW()-5)*$D$6:$D$18)
 
Last edited:
Upvote 0
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.

ABCDEFGHI
4DateSalesDay of the WeekTotal # ItemsTotal CountTotal Sales
5No. ItemsSalesMonday231$3,442.18
6ThursdayThursday, August 1, 201955$8,231.30Tuesday601$8,979.60
7FridayFriday, August 2, 201923$3,442.18Wednesday451$6,734.70
8SaturdaySaturday, August 3, 201929$4,340.14Thursday842$12,571.44
9SundaySunday, August 4, 201910$1,496.60Friday532$7,931.98
10MondayMonday, August 5, 201923$3,442.18Saturday412$5,298.14
11TuesdayTuesday, August 6, 201960$8,979.60Sunday101$1,496.60
12WednesdayWednesday, August 7, 201945$6,734.70Total31610$46,454.64
13ThursdayThursday, August 8, 201929$4,340.14
14FridayFriday, August 9, 201930$4,489.80
15SaturdaySaturday, August 10, 201912$958.00

<tbody>
</tbody>
privxls

Worksheet Formulas
CellFormula
G5=SUMIFS(C$6:C$15,$A$6:$A$15,$F5)
H5=COUNTIF($A$6:$A$15,F5)
I5=SUMIFS(D$6:D$15,$A$6:$A$15,$F5)
G12=SUM(G5:G11)
H12=SUM(H5:H11)
I12=SUM(I5:I11)
A6=TEXT(B6,"dddd")

<tbody>
</tbody>

<tbody>
</tbody>


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.
 
Upvote 0
You can also use WEEKDAY such as:

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

Excel 2010
ABCDEFG
6Don't needThursday, August 01, 2019$6,857Monday$14,906
7Friday, August 02, 2019$7,656Tuesday$10,234
8Saturday, August 03, 2019$9,985Wednesday$9,703
9Sunday, August 04, 2019$8,137Thursday$9,962
10Monday, August 05, 2019$5,770Friday$14,513
11Tuesday, August 06, 2019$5,807Saturday$14,375
12Wednesday, August 07, 2019$9,703Sunday$17,260
13Thursday, August 08, 2019$3,105
14Friday, August 09, 2019$6,857
15Saturday, August 10, 2019$4,390
16Sunday, August 11, 2019$9,123
17Monday, August 12, 2019$9,136
18Tuesday, August 13, 2019$4,427

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet28

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


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. :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top