Forum Discussion
Solved
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
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( ADDλ, LAMBDA(a,f, a+f), accumulated23, SCAN(0, food23, ADDλ), XLOOKUP(FALSE, ISNUMBER(food24), accumulated23 - food23) )I prefer the first though.
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( ADDλ, LAMBDA(a,f, a+f), accumulated23, SCAN(0, food23, ADDλ), XLOOKUP(FALSE, ISNUMBER(food24), accumulated23 - food23) )I prefer the first though.
PeterBartholomew1 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
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$34You 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!
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**
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.
