+ Reply to Thread
Results 1 to 14 of 14

SUM rows based on a variable date

  1. #1
    Registered User
    Join Date
    02-24-2016
    Location
    Oslo
    MS-Off Ver
    Office 365
    Posts
    58

    SUM rows based on a variable date

    Hi Guys,

    Iíve been struggling for a couple of days trying to solve a headache of mine. There are two problems who might be solved with the same solution (probably).

    1.Accumulated paid out equity/dividend
    Iím trying to SUM a range (row 44) based on the report date. Iím working in a budget which is 5 years going forward. The price (and IRR) depends on dividend and I want the report/budget to be as automatic as possible. Letís say Iím sending out a report as per. 31.12.2017 (the status date). The report should then sum up all dividend (row 44) in 2017. If I choose another date (f.ex. 31.12.2018) it should accumulate all dividend paid out in 2017 AND 2018. However, the dividend/payouts is usually in the end of each quarter (31.03, 30.06, 30.09 or 31.12) so if I choose the date 30.12.2017 (and the only payout in 2017 is in Q4) the payout should be zero. Now, the real problem is that my ďcriteria rangeĒ is based on quarters in the following format Q1-17, Q2-17, Q3-17 etc.

    As far as I understand it looks like Excel wonít understand that Q1-18 is GREATER than Q4-17.

    Below is the formula I'm using.

    =-SUMIFS(Likviditetesbudsjett!$D$44:$W$44;Likviditetesbudsjett!$D$4:$W$4;">="&Likviditetesbudsjett!$D$4;Likviditetesbudsjett!$D$4:$W$4;"<="&"Q"&ROUNDUP(MONTH($L$8)/3;0)&"-"&RIGHT(YEAR($L$8);2))


    2. Correct IRR-formula
    To calculate the correct IRR, the payouts should correspond with the right payout-date (at least the same year). Therefore I have created a table with preinserted dates (31.12.2017, 31.12.2018, 31.12.2019). Instead of accumulating all years it should only sum up the dividend/payout for the corresponding year.

    My formula shows as follows.

    =-SUMIFS(Likviditetesbudsjett!$D$44:$G$44;Likviditetesbudsjett!$D$4:$G$4;"<="&"Q"&ROUNDUP(MONTH($L$8)/3;0)&"-"&RIGHT(YEAR($L$8);2))

    The formula works for every date in the last quarter of each year (01.10-31.12), but as soon as I chose another date, the table only show zeros.

    Can anyone help me out here?

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: SUM rows based on a variable date

    You are correct - "Q4-17" is greater than "Q1-18" if they are text. It is just alphabetical in that case as to which is greater or less. I recommend you change all of your quarter text to actual dates and it appears the last day of each quarter is what you should use. Your conversion to text ("Q"&ROUNDUP(MONTH($L$8)/3;0)&"-"&RIGHT(YEAR($L$8)) is not going to help in this case.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    02-24-2016
    Location
    Oslo
    MS-Off Ver
    Office 365
    Posts
    58

    Re: SUM rows based on a variable date

    Thank you for the quick reply, PauleyB. The obvious answer is of course to change the quarters with actual dates or adding a help column (which in turn can be hidden). However, the quarters are used in many formulas in the worksheet and therefore I would like to be 100% sure that it can’t be done before I’m changing the original values.

    So, is there a way to make excel understand that Q1-18 is greater than Q4-17? Or is there another way (another formula) to get around this problem?

    Best regards

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,833

    Re: SUM rows based on a variable date

    Is it possible to reverse the text i.e 17-Q1, 17-Q2 etc so 18-Q1 > 17-Q4 ?

    Use Find/Replace to change

  5. #5
    Registered User
    Join Date
    02-24-2016
    Location
    Oslo
    MS-Off Ver
    Office 365
    Posts
    58

    Re: SUM rows based on a variable date

    Hi, JohnTopley.

    That’s actually a working idť. Thank you!

    However, it’s still not optimal since I would have to change all my formulas and all my budgets (also, it looks a bit weird and investors tends to notice “radical” changes).

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,833

    Re: SUM rows based on a variable date

    With dates in row 1,starting in A1

    Q1-17, Q2-17, Q3-17,Q4-17, Q1-18, etc

    in B2

    =IF(OR(AND(RIGHT(B1,2)>RIGHT(A1,2)),B1>A1),"Y","N")

    Replace "Y" and "N" with your formulae

    I cannot think of any other option other similar type of test comparing the year element with quarter element

    e.g RIGHT(B1,2)& MID(B1,2,1) > RIGHTt(A1,2)& MID(A1,2,1) i.e. 172>171


    Lesson here is obvious : NEVER use text for dates!

  7. #7
    Registered User
    Join Date
    02-24-2016
    Location
    Oslo
    MS-Off Ver
    Office 365
    Posts
    58

    Re: SUM rows based on a variable date

    By all means, I agree with you and I’m thinking of changing the worksheet with dates.

    But since I’m using the roundup function in a numerous other cells (making every date in Q1 (01.01-31.03) appear as Q1 I would have to change the formula to make any date in Q1 appear as 31.03, Q2 as 30.06, Q3 as 30.09 and Q4 as 31.12.

    However, if there is no possibility to format the “criteria range” in the sum if formula I can’t see any other ways to accomplish the task.

    I found the above-mentioned answer (RIGHT(B1,2)& MID(B1,2,1) > RIGHTt(A1,2)& MID(A1,2,1) i.e. 172>171) to be (almost) spot-on, but it would be (as far as I can see) impossible to implement it in a sumifs-formula to accumulate running dividends based on a variable date. Is that right?

    Thanks for the answers.

  8. #8
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: SUM rows based on a variable date

    Looks like you all resolved this on 'European' time.

    Quote Originally Posted by JohnTopley View Post
    Lesson here is obvious : NEVER use text for dates!
    Could not agree more. Think we have all run into this problem early on in our Excel careers, and you finally just naturally avoid it.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,833

    Re: SUM rows based on a variable date

    Look at SUMPRODUCT rather SUMIFS: it should handle the construct

    RIGHT(B1,2)& MID(B1,2,1) > RIGHTt(A1,2)& MID(A1,2,1)

    If you use SUMPRODUCT use finite ranges rather than whole column ranges i.e SUMPRODUCT (A2:A5000.....) rather than SUMPRODUCT((A:A ....)


    e.g with data to be SUMMED in C1:C4 and DATES in A and B


    =SUMPRODUCT((C1:C4)*(RIGHT(B1:B4,2)& MID(B1:B4,2,1) > RIGHT(A1:A4,2)& MID(A1:A4,2,1)))

  10. #10
    Registered User
    Join Date
    02-24-2016
    Location
    Oslo
    MS-Off Ver
    Office 365
    Posts
    58

    Re: SUM rows based on a variable date

    Sorry for the late reply here, but I’ve been traveling Europe without my laptop.

    Thank you all for the quick and helpful answers. I was about to post my spreadsheet with the working formulas, but couldn’t upload any attachment (trying both Explorer, Chrome, Opera and Edge).

    Using sumproduct I managed to obtain 99% of what I wanted (all though it might be some ugly formulas going forward..). However, I still can’t distinguish between dates, ie. between 30.12.2017 and 31.12.2017. I guess it can’t be done without changing to actual dates or adding a help column.

    Excel.JPG

    The working formula:

    =-SUMPRODUCT((D5:O5)*(RIGHT("Q"&ROUNDUP(MONTH(D8)/3;0)&"-"&RIGHT(YEAR(D8);2);2)&MID("Q"&ROUNDUP(MONTH(D8)/3;0)&"-"&RIGHT(YEAR(D8);2);2;1)>=RIGHT(D2:O2;2)&MID(D2:O2;2;1)))

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,833

    Re: SUM rows based on a variable date

    I still can’t distinguish between dates, ie. between 30.12.2017 and 31.12.2017
    .... don't understand why ???Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  12. #12
    Registered User
    Join Date
    02-24-2016
    Location
    Oslo
    MS-Off Ver
    Office 365
    Posts
    58

    Re: SUM rows based on a variable date

    Thanks for the didactic answer. For some reason I tried to use the "Attachment"-button in the advanced reply.

    You can see from the two attached workbooks there is a limitation using quarters when I want something to happen at a specific date in each quarter.
    As mentioned above the dividend/payouts is usually in the end of each quarter (31.03, 30.06, 30.09 or 31.12) so if I choose the date 30.12.2017 (and the only payout in 2017 is in Q4) the payout should be zero. However, I can't find a solution to solve this problem when I use quarters.

    Btw, I couldn't (of course) agree more with the statement regarding dates.

    Regards.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,833

    Re: SUM rows based on a variable date

    It is case of wanting your cake and eating it!

    As you have determined, you need to use dates [rather than the generic month-based quarter calculation] if you want a variable end date for any quarter as per your "Excel trouble - dates" file.

    Put the dates in row 3 with font "white" as your "helper" row, Quarter headings in row 2 and formula as

    =-SUMIFS(D5:O5,D3:O3,">="&D7,D3:O3,"<="&D8)

  14. #14
    Registered User
    Join Date
    02-24-2016
    Location
    Oslo
    MS-Off Ver
    Office 365
    Posts
    58

    Re: SUM rows based on a variable date

    Well, thanks for your time.

    To summarize for everyone who would like to make old budgets and valuation models (which uses text as dates) more dynamic, IT CAN’T BE DONE! Sit down and change the dates to actual dates (although it looks weird in a presentation).

    However, you can use sumproduct if you want to manipulate a whole row (instead of just a cell) instead of sumifs (were the “criteria range” can’t be formatted using formulas).

    I’ll try to figure out how to mark this thread as solved.

    Regards

+ 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. auto-populate a date based on date began and other variable
    By rcm4486 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-17-2017, 10:05 AM
  2. auto-populate a date based on date began and other variable
    By rcm4486 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2017, 07:58 AM
  3. [SOLVED] Print certain rows of certain columns based on variable
    By Gaellus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2017, 08:06 AM
  4. Replies: 1
    Last Post: 04-06-2016, 07:02 AM
  5. Highlight Rows Based on Date Range Using Static Date and Current Date
    By SaraStravers in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-23-2015, 07:38 PM
  6. [SOLVED] Function to Sum a number of rows based on a variable
    By mstew9415 in forum Excel General
    Replies: 9
    Last Post: 06-23-2014, 09:43 PM
  7. Hide Multiple Rows Based upon a variable
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-22-2012, 01:03 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