# Thread: Return Sum of Multiple VLOOKUP results? Thanks:  5 Post #5341221 (1)Post #5341194 (1)Post #5341228 (1)Post #5341197 (1)Post #5341214 (1) Likes:  4 Post #5341197 (1)Post #5341221 (1)Post #5341228 (1)Post #5341194 (1)

1. ## Return Sum of Multiple VLOOKUP results?

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.

2. ## Re: Return Sum of Multiple VLOOKUP results?

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
4 Date Sales Day of the Week Total # Items Total Count Total Sales
5 No. Items Sales Monday 23 1 \$3,442.18
6 Thursday Thursday, August 1, 2019 55 \$8,231.30 Tuesday 60 1 \$8,979.60
7 Friday Friday, August 2, 2019 23 \$3,442.18 Wednesday 45 1 \$6,734.70
8 Saturday Saturday, August 3, 2019 29 \$4,340.14 Thursday 84 2 \$12,571.44
9 Sunday Sunday, August 4, 2019 10 \$1,496.60 Friday 53 2 \$7,931.98
10 Monday Monday, August 5, 2019 23 \$3,442.18 Saturday 41 2 \$5,298.14
11 Tuesday Tuesday, August 6, 2019 60 \$8,979.60 Sunday 10 1 \$1,496.60
12 Wednesday Wednesday, August 7, 2019 45 \$6,734.70 Total 316 10 \$46,454.64
13 Thursday Thursday, August 8, 2019 29 \$4,340.14
14 Friday Friday, August 9, 2019 30 \$4,489.80
15 Saturday Saturday, August 10, 2019 12 \$958.00
privxls

Worksheet Formulas
Cell Formula
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")

3. ## Re: Return Sum of Multiple VLOOKUP results?

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

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

4. ## Re: Return Sum of Multiple VLOOKUP results?

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
4 Date Sales Day of the Week Total # Items Total Count Total Sales
5 No. Items Sales Monday 23 1 \$3,442.18
6 Thursday Thursday, August 1, 2019 55 \$8,231.30 Tuesday 60 1 \$8,979.60
7 Friday Friday, August 2, 2019 23 \$3,442.18 Wednesday 45 1 \$6,734.70
8 Saturday Saturday, August 3, 2019 29 \$4,340.14 Thursday 84 2 \$12,571.44
9 Sunday Sunday, August 4, 2019 10 \$1,496.60 Friday 53 2 \$7,931.98
10 Monday Monday, August 5, 2019 23 \$3,442.18 Saturday 41 2 \$5,298.14
11 Tuesday Tuesday, August 6, 2019 60 \$8,979.60 Sunday 10 1 \$1,496.60
12 Wednesday Wednesday, August 7, 2019 45 \$6,734.70 Total 316 10 \$46,454.64
13 Thursday Thursday, August 8, 2019 29 \$4,340.14
14 Friday Friday, August 9, 2019 30 \$4,489.80
15 Saturday Saturday, August 10, 2019 12 \$958.00
privxls

Worksheet Formulas
Cell Formula
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")

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.

5. ## Re: Return Sum of Multiple VLOOKUP results?

Originally Posted by kweaver
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

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

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.

6. ## Re: Return Sum of Multiple VLOOKUP results?

Great. Thanks for the feedback!

7. ## Re: Return Sum of Multiple VLOOKUP results?

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