Results 1 to 7 of 7

Thread: SUMIFS with array that counts number of valid days within a date range

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIFS with array that counts number of valid days within a date range

    Hi there,

    I've tried to recreate two tables which I'm working with below:

    Start Date Finish Date Effective Days Total Amount Daily Amount Month Start of Month End of Month Monthly Amount
    04/09/2019 22/10/2019 49 1000 20.41 Sep-19 01/09/2019 30/09/2019 ???
    15/09/2019 15/10/2019 31 1500 48.39 Oct-19 01/10/2019 31/10/2019 ???
    01/10/2019 24/12/2019 85 2000 23.53 Nov-19 01/11/2019 30/11/2019 ???
    Dec-10 01/12/2019 31/12/2019 ???

    For the sake of this exercise, the first table would be A1:E4 (including headers) and the second table would be G1:J5.

    I would like to have a formula which fills in the J column. This would have to go row-by-row in the first table, calculating how many days fall between the Start of Month and End of Month in the second table, multiply that by the amount in the Daily Amount column, then add that to the loop down the rest of the rows.

    This is just a sample; unfortunately my real data is much larger!

    I've tried to name the topic because I'm pretty confident it'll need an array formula, but I'm quite happy to accept a non-array formula if one exists. It might not be a SUMIFS either which works; that's fine too.

    Please help!

  2. #2
    Board Regular jimrward's Avatar
    Join Date
    Feb 2003
    Location
    Kingdom of Fife
    Posts
    1,689
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS with array that counts number of valid days within a date range

    For completeness and understanding can you supply for your sample the values you would expect in j2:j5

    I am guessing
    j2 (30 * 20.41) = 612.30
    J3 (31 * 48.39) + j2 = 2112.39
    J4 (30 * 23.53) + j3 = 2818.29

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS with array that counts number of valid days within a date range

    Quote Originally Posted by jimrward View Post
    For completeness and understanding can you supply for your sample the values you would expect in j2:j5

    I am guessing
    j2 (30 * 20.41) = 612.30
    J3 (31 * 48.39) + j2 = 2112.39
    J4 (30 * 23.53) + j3 = 2818.29
    Thanks for the reply Jim! Not quite - here's a breakdown below.

    J2 I'd expect 1,325.21
    551.02 from the A2:E2 row (27 days @ 20.41), plus,
    774.19 from the A3:E3 row (16 days @ 48.39), plus,
    0 from the A4:E4 row

    J3 I'd expect 1,904.20
    448.98 from the A2:E2 row (22 days @ 20.41), plus,
    725.81 from the A3:E3 row (15 days @ 48.39), plus,
    729.41 from the A4:E4 row (31 days @ 23.54)

    J4 I'd expect 704.88
    0 from the A2:E2 row, plus,
    0 from the A3:E3 row, plus,
    705.88 from the A4:E4 row (30 days @ 23.54)

    J5 I'd expect 564.71
    0 from the A2:E2 row, plus,
    0 from the A3:E3 row, plus,
    564.71 from the A4:E4 row (24 days @ 23.54)

    That would equal to 4,500 in total (1,325.21 + 1,904.20 + 705.88 + 564.71).

  4. #4
    New Member
    Join Date
    Apr 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS with array that counts number of valid days within a date range

    I've not got a solution, but I've created a formula which would solve just for J2 as above.

    =(IF(IF(AND(A2>=H$2,B2<=I$2),TRUE,FALSE)=TRUE,B2-A2+1,IF(IF(AND(A2=H$2,A2<=I$2,B2>I$2),TRUE,FALSE)=TRUE,I$2-A2+1,IF(IF(AND(A2I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E2)+(IF(IF(AND(A3>=H$2,B3<=I$2),TRUE,FALSE)=TRUE,B3-A3+1,IF(IF(AND(A3=H$2,A3<=I$2,B3>I$2),TRUE,FALSE)=TRUE,I$2-A3+1,IF(IF(AND(A3I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E3)+(IF(IF(AND(A4>=H$2,B4<=I$2),TRUE,FALSE)=TRUE,B4-A4+1,IF(IF(AND(A4=H$2,A4<=I$2,B4>I$2),TRUE,FALSE)=TRUE,I$2-A4+1,IF(IF(AND(A4I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E4)
    It looks complex but really all it does is run through this logic for a single month (Sept 2019 in this example):
    If A2 and B2 are within the month, just use the difference between A2 and B2 to calculate the number of days needed to multiply by E2
    > If A2 is smaller than H2, and B2 is smaller than I2, use the difference between B2 and H2 as the number of days needed to multiple by E2
    >> If A2 is larger than H2 and smaller than I2, but B2 is larger than I2, use the difference between A2 and I2 as the number of days needed to multiply by E2
    >>> If A2 is smaller than H2 and B2 is larger than I2, use the difference between H2 and I2 as the number of days needed to multiple by E2
    >>>> If none of the above apply then there are 0 days in the month that match

    I just don't really know how to turn this into an array to go through columns A and B, one at a time and multiply the results by column E, relative to the dates which are in columns H and I.

  5. #5
    New Member
    Join Date
    Apr 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS with array that counts number of valid days within a date range

    Quote Originally Posted by PuGZoR View Post
    =(IF(IF(AND(A2>=H$2,B2<=I$2),TRUE,FALSE)=TRUE,B2-A2+1,IF(IF(AND(A2=H$2,A2<=I$2,B2>I$2),TRUE,FALSE)=TRUE,I$2-A2+1,IF(IF(AND(A2I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E2)+(IF(IF(AND(A3>=H$2,B3<=I$2),TRUE,FALSE)=TRUE,B3-A3+1,IF(IF(AND(A3=H$2,A3<=I$2,B3>I$2),TRUE,FALSE)=TRUE,I$2-A3+1,IF(IF(AND(A3I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E3)+(IF(IF(AND(A4>=H$2,B4<=I$2),TRUE,FALSE)=TRUE,B4-A4+1,IF(IF(AND(A4=H$2,A4<=I$2,B4>I$2),TRUE,FALSE)=TRUE,I$2-A4+1,IF(IF(AND(A4I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E4)
    Slightly incorrect formula above. I can't post the correct one for some reason... it's too long.
    Last edited by PuGZoR; Sep 5th, 2019 at 01:59 AM.

  6. #6
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,092
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SUMIFS with array that counts number of valid days within a date range

    Hi, here is an option that you can try:

    Excel 2013/2016
    ABCDEFGHIJ
    1Start DateFinish DateEffective DaysTotal AmountDaily AmountMonthStart of MonthEnd of MonthMonthly Amount
    204/09/201922/10/201949100020.4101/09/201901/09/201930/09/20191325.31
    315/09/201915/10/201931150048.3901/10/201901/10/201931/10/20191904.3
    401/10/201924/12/201985200023.5301/11/201901/11/201930/11/2019705.9
    501/12/201001/12/201931/12/2019564.72

    Sheet1



    Array Formulas
    CellFormula
    J2{=SUM(IF($B$2:$B$4>=H2,IF($A$2:$A$4<=I2,$E$2:$E$4*(1+IF($B$2:$B$4>=I2,I2,$B$2:$B$4)-IF($A$2:$A$4<=H2,H2,$A$2:$A$4)))))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    [code]your code[/code]

  7. #7
    New Member
    Join Date
    Apr 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS with array that counts number of valid days within a date range

    Quote Originally Posted by FormR View Post
    Hi, here is an option that you can try:

    Excel 2013/2016
    A B C D E F G H I J
    1 Start Date Finish Date Effective Days Total Amount Daily Amount Month Start of Month End of Month Monthly Amount
    2 04/09/2019 22/10/2019 49 1000 20.41 01/09/2019 01/09/2019 30/09/2019 1325.31
    3 15/09/2019 15/10/2019 31 1500 48.39 01/10/2019 01/10/2019 31/10/2019 1904.3
    4 01/10/2019 24/12/2019 85 2000 23.53 01/11/2019 01/11/2019 30/11/2019 705.9
    5 01/12/2010 01/12/2019 31/12/2019 564.72
    Sheet1

    Array Formulas
    Cell Formula
    J2 {=SUM(IF($B$2:$B$4>=H2,IF($A$2:$A$4<=I2,$E$2:$E$4*(1+IF($B$2:$B$4>=I2,I2,$B$2:$B$4)-IF($A$2:$A$4<=H2,H2,$A$2:$A$4)))))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Sir, you are a gentleman and a scholar. This worked BEAUTIFULLY. Thank you so much! If you're ever around Brisbane, Australia, I would very much like to buy you a beverage.

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
  •