+ Reply to Thread
Results 1 to 8 of 8

Sumif and Weekday function combined

  1. #1
    Registered User
    Join Date
    04-09-2015
    Location
    new york
    MS-Off Ver
    2010
    Posts
    18

    Sumif and Weekday function combined

    Hello,

    First time posting, would appreciate some help.

    I have a column with dates filled in vertically, lets say 10 or so dates. Lets call this column A. Each date has a corresponding $value in the column next to it. Lets call this column B. So 10 dates, and 10 $values. To the right I have ONE row with 10 dates, in each column heading (Column D through M), each date is 7 days apart. So for example, the first date is 4/9/2015, second date is 4/16/2015, etc all the way to the 10th date.

    So I want to create a sumif function where the equation looks at all the dates in the left column (column A) and compares it with the heading date in the row in the first column (column D). If any of the dates in the vertical column (Column A) are within that first week, then sum the corresponding values in the left column. They must only be within those 7 days. Then we move on to the second column, in the row, and the values have to be between 4/9/15 and 4/16/2015, and so forth.

    Basically i'm creating a 10 week cashflow statement and I want to know in which week the account receivables will be due and I want to avoid double counting. Thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Sumif and Weekday function combined

    welcome to the forum, unam. perhaps you could key in manually what you are hoping to see next time. maybe in D4:
    =SUMIFS($B:$B,$A:$A,">="&D3,$A:$A,"<"&D3+7)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sumif and Weekday function combined

    Hi unam and welcome to the forum,

    This looks like a SumProduct problem to me. Put this in D4 and pull across.

    =SUMPRODUCT(--($A$3:$A$12>=D3),--($A$3:$A$12<D3+7),$B$3:$B$12)

    I'm not sure what the Weekday has to do with the question.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    04-09-2015
    Location
    new york
    MS-Off Ver
    2010
    Posts
    18

    Re: Sumif and Weekday function combined

    Hi Benishiryo,

    I changed your formula a little bit from what you gave but I'm still off. The formula I'm putting in =SUMIFS($B:$B,$A:$A,">="&D3-7,$A:$A,"<"&D3) works but doesn't add more than one value. For example in the dates between 5/28/15 - 5/21/15 - there should be two values 34 and 66, to get 100, but I only get 66.

    Any recommendations? I added a version 2 to the file. Thanks!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-09-2015
    Location
    new york
    MS-Off Ver
    2010
    Posts
    18

    Re: Sumif and Weekday function combined

    Hi MarvinP,

    I tried your equation and its slightly off. I've added file version 3 with your equation and showed compared to expected results.

    Thanks for your help.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-09-2015
    Location
    new york
    MS-Off Ver
    2010
    Posts
    18

    Re: Sumif and Weekday function combined

    Actually never mind. My fault Benishiryo, the date was 2014. so the new updated formula works. Thanks.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sumif and Weekday function combined

    Perhaps you need a "<=" (less than or equal to) instead of a single less than. I don't know exactly the answer you are trying to arrive at.

  8. #8
    Registered User
    Join Date
    04-09-2015
    Location
    new york
    MS-Off Ver
    2010
    Posts
    18

    Re: Sumif and Weekday function combined

    I got it MarvinP - I just changed it to =SUMPRODUCT(--($A$3:$A$12>=D13-7),--($A$3:$A$12<D13),$B$3:$B$12) from +7 to -7 and that fixed it.

    Regarding why I asked for weekday initially, that's how I saw someone resolve it earlier so I was trying to play around it but couldn't figure it out. Its driving me mad. i guess there are multiple ways to solving all these.

    The logic behind weekday I guess is that if its withing that week and the week end in a certain day, then it should be included. I just cant get my head around how it was done.

    But anyway, thanks for your help - your way works as well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] SUMIF function combined with INDEX/MATCH
    By nickiw579 in forum Excel General
    Replies: 7
    Last Post: 01-27-2015, 11:40 PM
  2. [SOLVED] Sumif And countif combined function
    By Deventus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2013, 05:11 AM
  3. If function combined with weekday
    By ImPerplexed in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-15-2013, 08:20 PM
  4. Sumif Weekday Help
    By Alice21 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-14-2011, 11:19 AM
  5. sumif weekday
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2006, 03:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1