Forum Discussion

Thomas_Westerby's avatar
Thomas_Westerby
Copper Contributor
Jan 17, 2024

Automatically summing together groups of numbers in Excel.

Hello, 

I am trying to sum together groups of numbers in Excel based on groupings. 

 

As in the attached example, certain weights are grouped according to the Shop number. 

 

Is there a way to use a function to automatically calculate the total of all shops without having to use a formula for each total? 

 

I am aware of the SUM function and the autosum feature, but these require each total to be calculated separately and individually. 

 I hope my explanation makes sense, thank you 🙂 

  • Thomas_Westerby 

    =IF(B3="",SUM(INDEX($B$1:B2,LARGE(IF($A$1:A1="",ROW($A$1:A1)),1)+1):INDEX($B$1:B2,ROW())),"")

     

    You can apply this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

     

    =HSTACK(TOCOL(UNIQUE(A2:A15),1),BYROW(TOCOL(UNIQUE(A2:A15),1),LAMBDA(x,SUMIFS(B2:B15,A2:A15,x))))

     

    With Office 365 or Excel for the web you can use this formula.

Resources