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.
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.
- 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 ?
If you remove the 0 in row 23 then you would remove a formula. Is that really what you want?
- No, this would not be ideal. I believe the solution suggested by Peter may work. Thanks Raj