SOLVED

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

Brass Contributor

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

@Mr_Raj_C 

Try this for P18.

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

 

 

@Detlef_Lewin 

 

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 

**


@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 ?

@Mr_Raj_C 

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

@Mr_Raj_C 

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.

 

@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

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

@Mr_Raj_C 

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) )

 

@Mr_Raj_C 

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!

@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.

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

@SergeiBaklan 

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.

1 best response

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

@Mr_Raj_C 

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.

 

View solution in original post