Forum Discussion
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( 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!
PeterBartholomew1 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.
