Formula for a Moving YTD Total

[JasonMcQueen]

Hi, can anyone help me with a formula?

Looking at the attachment, what I'm trying to do is use the drop down options in cells A2 and A4 to provide moving calculation points for both the current period date and the YTD totals. I've figured out how to do the current period but the YTD is eluding me. I'd like to only calculate the YTD value up to the cell value in A4 i.e if Period 3 is selected it counts periods 1,2 and 3 and if period 6 is selected it counts 1-6 etc etc. The data is pulled from the respective columns on the GRP sheet.

[amit.wilson]

Hi Jason,

one way to do this is to change the the "Period" data-type from text (e.g. "P3") to number (e.g. 3).

Then, to add YTD, you can run SumIfs() and add for all periods less than and equal to 3.

I've made these changes to your sheet attached.

-- I've changed "Period" data form text to numbers on "validation" sheet and on "GRP" sheet.

-- I've set this formula in D27:

=SUMIFS(GRP!J:J,GRP!A:A,'KPI SHEET'!A2,GRP!B:B,"<="&'KPI SHEET'!A4)

It's the same as your formula in C27 except for the "less than and equal to" bit in colour above.


-- I've also changed the format of the numbers in A4 on "KPI SHEET" and column B on "GRP". It's like adding a dollar sign to a number. So all the numbers appear to be "P1", "P2" and so on.

When you enter data in column B on "GRP" remember to enter just a number and not "P3".


Is the sheet working as it should?

Cheers

[JasonMcQueen]

Thats great. Many thanks.