Sumif To Add Cumulative YTD Criteria In The Same Column

[jaykay]

Hello guys,

I have used sumiff to add up my inter-company recharges based on dept, finanical year, and financial period (e.g month).

the formula below works by saying

=IFERROR(SUMIFS(data!$D$2:D92166,data!$B$2:B92166, $D$2,data!$C$2:C92166,$D$3,data!$A$2:A92166,$D$4),"-")

look at the data range data!$D$2:D92166 and sum dependent on the chosen financial year ($D$2), financial period ($D$3), and department ($D$4).

I am however struggling to find a formula that show the cumulative year-to-date amounts if say March is chosen. It should show the sum of January to March from the same column that contains various entries from Jan to Dec.
I'm not sure i can use SUMPRODUCT with ISNUMBER as i want to add all amount from Jan to the chosen period. I have used numbers to represent the periods (e.g. 1 to 12).

Any help would be greatly appreciated.

Thanks,
Jay

[Excel Fox]

Post a sample workbook with the expected output for 1 or 2 months as example.

[jaykay]

I have tried attaching workbook but the file is to big and wont be accepted.

In the workbook

When the financial year, financial period, and department are selected with the drop down menu, it populate the recharge amounts in the section below for the period. This then compares vs budget to show variance.

Financial Year FY 1314
Financial Period 3
Department - Energy


Budget Actual £ Var % Var
Period £150.0 k £200.0 k £(50.0)k >99%
Year to Date £450.0 k £450.0 k (100.0%)


What i would also need to do if have a formula in the empty cell for year-to-date that shows the year to date recharge amount, for example for the Energy department in Fin Year 2013/14, Period 3, would give a cumulative amount of £7k (period one is £1k, plus period two £2k, plus period three £4k). The data is

Dept Fin_Yr Fin_Pd Amount
Safety FY 1213 1 1000
Energy FY 1314 1 2000
Safety FY 1213 2 5000
Energy FY 1314 2 10000
Safety FY 1314 3 100000
Energy FY 1314 3 200000


Any help would be greatly appreciated, having spend some hours trying to figure it out.

Thank you.

[Safal Shrestha]

Find the attachment and see if it can help you any way.


Bs,

[Kevin@Radstock]

Post a sample not the whole book!


Post a sample workbook with the expected output for 1 or 2 months as example

[jaykay]

Sahel,

Thanks very much for the tip, it finally works after using your formula in the below format

=SUMPRODUCT(--(Dept=$D$4),--(Fin_Pd<=$D$3),--(Fin_Year=$D$2),--(Amount))

I have tried attaching a sample of my output but still too large. Your attachment however is exactly what i need.

Thanks again, much appreciated.

Jay

[jaykay]

I should spell your name correctly Safal, apologies

[Safal Shrestha]

U r welcome and apologies accepted.