Formulae to sum totals from duplicate references

[fauxlidae]

I'm trying to build some code that will scan a set of data in column F for a duplicate text reference and, once a duplicate is identified, will sum the associated number in column M of the same row. Each duplicate reference should be summed separately, i.e, a payment of £15 and a payment of £10, both with the duplicate reference 'Josh', need to be added together, and a payment of £7 and £9 for duplicate reference 'Sarah' need to be summed together in a separate total.

Column M also contains some N/A references, and the code needs to exclude these from any attempt to sum - i.e, if 'Josh' also has a row with N/A, then this musn't stop Excel from summing the £15 and £10.

Once these amounts are conjugated into a single row (i.e, a row where Josh has a total of £25), the individual rows that were added together need to be deleted.

I have some VBA knowledge, but it's quite basic and i'm not sure how to build up a multi-stage process like this.

Any help much appreciated!

 

[bassplr19]

Simplest solution would be to change the N/A to 0 and use sumif

 

[fauxlidae]

Simplest solution would be to change the N/A to 0 and use sumif

Unfortunately the N/A is an #N/A error generated by Excel - sorry, should have specified that! The result of a VLOOKUP not finding any data

 

[bassplr19]

Use ifna(vlookup(lookup info),0)

 

[bassplr19]

Also, probably use xlookup instead

 

[alansidman]

Group By in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Spend] <> "N/A")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Customer reference"}, {{"Total", each List.Sum([Spend]), type number}})
in
    #"Grouped Rows"

Book11
	A	B	C	D	E
2	Customer reference	Spend		Customer reference	Total
3	Josh	10		Josh	20
4	Sarah	7		Sarah	16
5	Ryan	12		Ryan	12
6	Josh	N/A
7	Sarah	9
8	Josh	10
Sheet1

 

[awoohaw]

a different way to try it:

Mr excel questions 63.xlsm
	A	B	C	D	E	F	G
1
2		Customer Reference	Spend
3		Josh	10		Josh	20
4		Sarah	7		Sarah	22
5		Ryan	12		Ryan	9
6		Josh	#N/A
7		Sarah	9
8		Josh	10
9
fauxlidae

Cell Formulas
Range	Formula
E3:E5	E3	=UNIQUE(B3:B8)
F3:F5	F3	=SUM(($B$3:$B$8=E3)*IF(ISNA(C3:C8),0,C3:C8))
C6	C6	=NA()
Dynamic array formulas.

This won't auto delete the rows when you get to 25. But you can use the rules for conditional formatting to highlight the rows.