Forum Discussion

Mr_Raj_C's avatar
Mr_Raj_C
Brass Contributor
Dec 04, 2023

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

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

2 Replies

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

    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!

    • Mr_Raj_C's avatar
      Mr_Raj_C
      Brass Contributor
      Dec 05, 2023

      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.