Sum Finance year - based on current date

[trprasad78]

I have four Financial year will be their in out put.
First one always be a current financial year
2nd one following next financial year
3rd following next
4th following next.

When ever current date change the out also change.
If current date is 05-Feb-17 then sum FY17= Apr16 to mar17
Rest of the column should be following next 3 frequent year..FY18,FY19&FY20.

We need to maintain next 3 years.

If current date is 20-Dec-2017 then sum FY18 = Apr17to Mar18.

Note : 50 excel file have same format , but months will increase or decrease based on the project.

Hope I am clear.

Sample file attached, please help me.

 

[PCosta87]

Hi,

Please refer to attachment.

Hope this helps

 

[trprasad78]

Hi,

Please refer to attachment.

Hope this helps

Thank you so much

 

[PCosta87]

Thank you so much

Don't mention it

 

[trprasad78]

Don't mention it

I have few more files, where i have number of revenue and cost rows are more,
it has to add next line.

is that possible to get in VBA ? sample file attached

 

[PCosta87]

Hi,

Not sure I understood... Can you elaborate on your requirement?
If you want to sum all revenue rows in the same cells (yellow), you can simply add more SUMIF functions like so: "SUMIF(...)+SUMIF(...) and so on, until you get all rows you need.

 

[trprasad78]

Hi,

Not sure I understood... Can you elaborate on your requirement?
If you want to sum all revenue rows in the same cells (yellow), you can simply add more SUMIF functions like so: "SUMIF(...)+SUMIF(...) and so on, until you get all rows you need.

We got if we have one cost and one revenue.
How many revenue we have that many rows in out put sheet.

As you did for one revenue/cost.
It has to add in next rows

Hope now clear ? Please revert if you More questions

 

[PCosta87]

Hi,

I think I understand now... no need for VBA.
Changed the formula slightly so you can now simply copy and paste (refer to attachment).

Make sure to paste with 1 row of interval since we are dealing with 2 rows at a time:


Then simply delete the empty rows:


If you have any further questions, feel free to ask

 

[Haseeb A]

Hello Prasad,

In E4 in Output,

=SUMPRODUCT(ISNUMBER(SEARCH(E$3,'Costing Delivery'!$A$18:$A$31))*('Costing Delivery'!$E$16:$AP$16=LOOKUP("zzzz",$E$2:E$2)),'Costing Delivery'!$E$18:$AP$31)

then copy across other cost & revenue. Note:use comma as in red highlighted instead of *