Forum Discussion

Mr_Raj_C's avatar
Mr_Raj_C
Brass Contributor
Dec 03, 2023
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...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Dec 04, 2023

    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.

     

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Dec 03, 2023

Mr_Raj_C 

Try this for P18.

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

 

 

Mr_Raj_C's avatar
Mr_Raj_C
Brass Contributor
Dec 04, 2023

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

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Dec 04, 2023

    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.

     

    • Mr_Raj_C's avatar
      Mr_Raj_C
      Brass Contributor
      Dec 04, 2023
      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 ?
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Dec 04, 2023

        Mr_Raj_C 

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

         

Resources