Array, Group, Sub-total?

[dgm511]

Hi

I'm struggling to find the correct method for totalling many rows of data where the common link is a reference number. Example attached from a list of over 10,000 rows. Each reference number will have between 1 and 12 values and I'm looking for a quick way to sum the totals by reference number and display for example <Ref>000015 <Total>995.41.

Any help pointing me in the right direction would be gratefully received.
Many thanks

 

[ETAF]

for the ref use
=UNIQUE(A:.A)
for the sum
=SUMIF(A:.A,G3,B:.B)

OR a pivot table
Various other methods

what version of excel are you using

 

[dgm511]

MS365

 

[dgm511]

for the ref use
=UNIQUE(A:.A)
for the sum
=SUMIF(A:.A,G3,B:.B)

OR a pivot table
Various other methods

what version of excel are you using

Very many thanks...I will learm more about SUMIF. Thank you again

 

[ETAF]

in 1 cell you can put

=HSTACK(UNIQUE(A:.A),SUMIFS(B:.B,A:.A,UNIQUE(A:.A)))

 

[dgm511]

in 1 cell you can put

=HSTACK(UNIQUE(A:.A),SUMIFS(B:.B,A:.A,UNIQUE(A:.A)))

Very cool - adding HSTACK to my homework!

 

[ETAF]

and SUMIFS
and also the .
this removes empty cells - like trimrange

i came across the video , on my subscription , which explains the .DOT
B:.B

its worth watching the whole video - BUT the text has shortcuts to parts of the video , covering various info

5:59 Dot operator in Excel (Trim Refs)

 

[AlanSidman]

Alternatively you can use Power Query or the GroupBy Function. Both are demonstrated in the attached file.

 

[p45cal]

Also pivot table.

See attached at cell D2.

 

[AliGW]

Try this:

=GROUPBY(A:.A,B:.B,SUM,3,0)