Results 1 to 7 of 7

Thread: Return Sum of Multiple VLOOKUP results?

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    226
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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")
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default 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)

    Last edited by kweaver; Sep 12th, 2019 at 08:51 PM.

  4. #4
    New Member
    Join Date
    Nov 2016
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return Sum of Multiple VLOOKUP results?

    Quote Originally Posted by Toadstool View Post
    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")

    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.

  5. #5
    New Member
    Join Date
    Nov 2016
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return Sum of Multiple VLOOKUP results?

    Quote Originally Posted by kweaver View Post
    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. #6
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return Sum of Multiple VLOOKUP results?

    Great. Thanks for the feedback!

  7. #7
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,234
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    Two plus two equals five for large values of two.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •