Look up to Duplicate cells and return the sum total of each unique duplicate cell

[dhome]

Good afternoon to everyone,

I hope your all getting used to the new normal while we try to counteract COVID19.

I have two sheets

Here is a small summary of my data set

Sheet 1 - My lookup is in column A 0054 and my formula is in column F - 137.70

Sheet 2 - is the table array with duplicate values Column A:B

I would like a formula to lookup and sum duplicate reference numbers so sheet A in this example ref 0054 would agree to the total of 497.70 and would pull that amount into the cell and not 137.70



Sheet 1

0054

497.7

08/09/2020

CLAIRE

137.7

CHECK

Sheet 2

0054	137.7	Sales Payment	Faststat Limited			497.7​	check
0054	360	Sales Payment	SKS			497.7​	check

Thank you for your help

 

[dhome]

Dates

 

[Fluff]

In that case I'm not sure why it's not working for you.
Are the values in col A exact matches?

 

[dhome]

Yes exact

 

[dhome]

I have to go out can we come back to this in the morning please?

 

[Fluff]

Unfortunately I'm not sure what to suggest, as you can see in post#8 the formula works for me.

 

[dhome]

Hi

This is the top three rows of the results of the formula you can see these are all single unique ref 33.34.35 but the result is zero for all id expect 612,253.30 and 2841

01/09/2020	H JENKINSON & CO, ADBOARDS LTD	923 - BANK TRANSFER - CREDIT	33		612	08/09/2020	CLAIRE	0	CHECK
01/09/2020	ALLSORTS OFF LTD, ALLSORTS	923 - BANK TRANSFER - CREDIT	34		253.3	08/09/2020	CLAIRE	0	CHECK
01/09/2020	HAMPSHIRE COUNTY C, 1000 9705139547 K	923 - BANK TRANSFER - CREDIT	35		2841	08/09/2020	CLAIRE	0	CHECK

 

[dhome]

Hi

I have fixed that the next problem is this: the formula is bringing in the same figure for the next reference down and ref 55 should be 1843.52 not 497.70.

Sheet 1

54		497.7	08/09/2020	CLAIRE	497.7
55		1843.52	08/09/2020	CLAIRE	497.7

Sheet 2

54	137.70	Sales Payment	Faststat Limited			497.7​
54	360.00	Sales Payment	SKS			497.7​
55	1843.52	Sales Payment	Office Friends Essentials Ltd			1843.52​

 

[Fluff]

Which is exactly what the the formula does

+Fluff v2.xlsm
	A	B	C	D	E	F
1
2	54		497.7	08/09/2020	CLAIRE	497.7
3	55		1843.52	08/09/2020	CLAIRE	1843.52
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=SUMIFS(Sheet2!B:B,Sheet2!A:A,A2)

 

[dhome]

Hi,

My mistake earlier as this is the formula that works - which was your formula

=SUMIFS('Nominal Download'!H:H,'Nominal Download'!G:G,'Nominal Download'!G2) however it does not work when duplicates appear

My sheet 1 just has sequential numbers sheet 2 has the duplicates

55		1843.52	08/09/2020	CLAIRE	497.7
56		330.36	08/09/2020	CLAIRE	1843.52

 

[dhome]

Hi

Sorry to bother you and THANK YOU for your help previous are you able to assist as per above

Thank you