SOLVED

# Adding totals for two separate periods only where there is data.

Brass Contributor

# Adding totals for two separate periods only where there is data.

Dear Excel Community,

I'm hoping someone can assist with my conundrum !

Please see attached workbook. In the spreadsheet, i'm looking at data between two periods, 2022/23 vs 2023/24. The data is broken down by month.

Because we are still working through 2023/24, the YTD figures are not comparable. What i would like to create is a dynamic formula that adds up all the data entered for 2023/24 and then compares to the same period for the previous year. As more data gets added to another month in 2023/24, the total reflects this and then selects the corisponding period for the previous year.

Hope the above makes sense ?

I would like to have the total display in the "Period comparison" column.

As always, thanking you all for your help to date and i'm hopeful someone can assist !

Thanks

Raj

14 Replies

# Re: Adding totals for two separate periods only where there is data.

Try this for P18.

``=SUM(C18:INDEX(C18:N18,MATCH(TEXT(TODAY(),"MMMM"),C\$5:N\$5,0)-1))``

# Re: Adding totals for two separate periods only where there is data.

Thank you for this suggestion. It kind of does the trick as i assume it's adding up based on the current month -1.

Is there anyway to modify this formula so that it only adds up cells based on data entered some where else ? i.e matching the months containing data in line 23 ? eg line 23 only contains data up till October so the calculation for P18 should be based on how many months data is in line 23 ?

Hope that makes sense ?

If not, i will go with your suggestion.

Thanks Raj

# Re: Adding totals for two separate periods only where there is data.

**

@Mr_Raj_C wrote:

Thank you for this suggestion. It kind of does the trick as i assume it's adding up based on the current month -1.

Correct.

@Mr_Raj_C wrote:

Is there anyway to modify this formula so that it only adds up cells based on data entered some where else ? i.e matching the months containing data in line 23 ? eg line 23 only contains data up till October so the calculation for P18 should be based on how many months data is in line 23 ?

Row 23 contains data for 12 months. 0 is also considered as data, as it is always possible to have months with zero sales (or whatever). Think about the COVID pandemic or a natural disaster.

# Re: Adding totals for two separate periods only where there is data.

Thank you and a valid point. If i were to remove the "0" and leave it blank (where data is not available at present) is there a possible solution ?

# Re: Adding totals for two separate periods only where there is data.

If you remove the 0 in row 23 then you would remove a formula. Is that really what you want?

best response confirmed by Mr_Raj_C (Brass Contributor)
Solution

# Re: Adding totals for two separate periods only where there is data.

A couple of 365-specific ideas.  The first is to take a 22/23 value into account only if the corresponding 23/24 cell contains a number:

``= SUM(FILTER(food23, ISNUMBER(food24)))``

The second is to accumulate a running total up to but not including the first blank cell

``````= LET(
XLOOKUP(FALSE, ISNUMBER(food24), accumulated23 - food23)
)``````

I prefer the first though.

# Re: Adding totals for two separate periods only where there is data.

@Peter Bartholomew Thank you for the suggestion and i believe the first example you have mentioned should do the trick !

I'm trying to implement it into my spreadsheet but with no luck. I'm sure I'm missing a few steps ? Seems i need to define a list perhaps ?

I'm afraid my Excel knowledge is not comparable to your level :)

Thanks Raj

# Re: Adding totals for two separate periods only where there is data.

No, this would not be ideal. I believe the solution suggested by Peter may work. Thanks Raj

# Re: Adding totals for two separate periods only where there is data.

As variant

``````=LET(
n, XMATCH(TRUE,C23:N23>0,0,-1),
YoY, SUM(TAKE(C23:N23,,n))/SUM(TAKE(C20:N20,,n))-1,
IFERROR(YoY, 0) )``````

# Re: Adding totals for two separate periods only where there is data.

The things that could go wrong are to try to use the formula in a legacy version of Excel; FILTER only exists in the dynamic array versions of Excel (365 and 2021).  Also the defined names 'food23' and 'food24' must be correctly specified (using Name Manager or the AFE).

``````food23
='Waste Board'!\$C\$33:\$N\$33

food24
='Waste Board'!\$C\$34:\$N\$34``````

You could simply use the range references directly but having once described the A1 notation as 'an abomination that has no place in any computing environment' I am slightly reluctant to work without defined names!

# Re: Adding totals for two separate periods only where there is data.

@Peter Bartholomew Thank you. I will have a play around and try and get it to work.

We are definitely using Office 365 at work, so the the FILTER function should work fine.

# Re: Adding totals for two separate periods only where there is data.

@SergeiBaklan Thank you for the alternative option. i will give it a try.

# Re: Adding totals for two separate periods only where there is data.

A nice thing about your XMATCH/TAKE combination is that it returns a range reference so, in other circumstances, one could still use SUMIFS etc. to refine the calculation further.

# Re: Adding totals for two separate periods only where there is data.

1 best response

Accepted Solutions
best response confirmed by Mr_Raj_C (Brass Contributor)
Solution

# Re: Adding totals for two separate periods only where there is data.

A couple of 365-specific ideas.  The first is to take a 22/23 value into account only if the corresponding 23/24 cell contains a number:

``= SUM(FILTER(food23, ISNUMBER(food24)))``

The second is to accumulate a running total up to but not including the first blank cell

``````= LET(