Sum invoice payments without duplicates

Hi and welcome to the forum.

One way with a helper column. In D2 copied down

Formula:

Please Login or Register  to view this content.

then sum column D

Or as an array formula entered with Ctrl-Shift-Enter

Formula:

Please Login or Register  to view this content.

This works on your posted sample data.

Data Range

	A	B	C	D	E
1	Invoice	Qty	Amount Due		Total
2	1234	2	120		530
3	1234	4	120
4	1356	4	230
5	1895	3	180
6	------	------	------	------	------

=SUM(IF(FREQUENCY(MATCH(A2:A5,A2:A5,0),ROW(A2:A5)-ROW(A2)+1),C2:C5))

It will not work if there are duplicate invoice numbers but different amounts due.

1234...120
1234...100

With your data in the range A2:B29...

This formula:

=SUM(IF(FREQUENCY(MATCH(A2:A29,A2:A29,0),ROW(A2:A29)-ROW(A2)+1),B2:B29))

Returns 3758.44 which is the correct result.

Originally Posted by kgkgkg9009

Thanks for responding! I tried the formula you posted from the data I initially provided but it won't work on my actual data.
Below is my actual data but. I just copy and pasted the invoice and amount columns but there are about 5 or 6 columns in between them with data.
As you can see, it is the same concept where there are many duplicate invoice #'s because they have lines with different order information but the amount is still doubled.

Hi,

It's important when you post a question to mention and show all relevant data, and make sure it is at least representative as to layout, otherwise we all spend time devising solutions that won't work on your actual data.
Your original contained three columns of data all adjacent. This latest shows just two adjacent columns yet you say that there are 5 or 6 columns in between the data.

If we don't know there are intermediate columns and if even you can't say whether it's 5 or 6 columns how can we possibly know.

The formulae I gave you WILL work provided you adjust the ranges to reflect your actual data. I'm attaching an example but you'll need to adjust the formula ranges when you've decided where the Invoice and Amount columns actually are.

One way is to expand the range to make sure you get all the data.

=SUM(IF(FREQUENCY(IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1),B2:B100))

This formula has to be array entered.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You're welcome. Thanks for the feedback!

Originally Posted by Teethless mama

Try this one. It's shorter and it doesn't need to array entered

And it's also a lot less efficient (slower to calculate).