Is it possilble to use a calculated row reference in a formula?

John Knipper

I have a table (2x12) of data (sales $) for each month (one row per month) in the Current Year(CY) and Previous Year (PY) columns. I'm trying to calculate the YTD sales (by month) of CY and PY based on today's date using the function, Today().

I know I can use a formula like PY-YTD = SUM(PY1:PY4) to sum the first four rows (Jan-Apr) on the PY sales. This works for the month of May. Upon entering the month of June, 'PY4' will need to be updated to 'PY5'.

I know I can calculate the needed row by using MON(TODAY())-1. e.g. on May 4th, MON(TODAY())-1=4; on Jun 1, MON(TODAY())-1 = 5. Is there a way I can use 'MON(TODAY())-1' in a formula like this: SUM(PY1:PY'MON(TODAY()-1') to enable the calculation to use the correct row number without the need to update each month?

Thank you, John

Heather Duff

Hi @John Knipper

Try this in your CY running total cell: =SUMIF(Month:Month, <MONTH(TODAY()), CY:CY)

And this in your PY running total cell: =SUMIF(Month:Month, <MONTH(TODAY()), PY:PY)

Both of these will basically sum any fields (either in CY or PY, depending on the formula) that are associated with a month number that is smaller than the current month.

If you want to include the current month's totals in the running totals, you'll simply change < to <=.

Let me know if it works for you!

Best,

Heather