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?