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?

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

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

Use Find/Replace to change

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

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!

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.

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

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

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.

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.

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)

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