Is there a way to remove duplicate in an formula?? Can you please assist on this formula

[Jarodjp42]

I only know of the way to remove duplicates by going to data tab and under data tools click remove duplicates. Is there a way to sum the total amounts of invoices and remove the duplicates?

Question: Calculate the total sum of the invoices. If there are duplicates invoices, only use the total 1 time?


[TABLE="width: 141"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Ivoice[/TD]
[TD]Totals[/TD]
[/TR]
[TR]
[TD]INV-000014[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD]INV-000014[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD]INV-000021[/TD]
[TD="align: right"]223[/TD]
[/TR]
[TR]
[TD]INV-000021[/TD]
[TD="align: right"]223[/TD]
[/TR]
[TR]
[TD]INV-000021[/TD]
[TD="align: right"]223[/TD]
[/TR]
[TR]
[TD]INV-000021[/TD]
[TD="align: right"]223[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000110[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD]INV-000110[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD]INV-000110[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD]INV-000117[/TD]
[TD="align: right"]1546[/TD]
[/TR]
[TR]
[TD]INV-000117[/TD]
[TD="align: right"]1546[/TD]
[/TR]
[TR]
[TD]INV-000117[/TD]
[TD="align: right"]1546[/TD]
[/TR]
[TR]
[TD]INV-000117[/TD]
[TD="align: right"]1546[/TD]
[/TR]
[TR]
[TD]INV-000118[/TD]
[TD="align: right"]998[/TD]
[/TR]
[TR]
[TD]INV-000118[/TD]
[TD="align: right"]998[/TD]
[/TR]
[TR]
[TD]INV-000118[/TD]
[TD="align: right"]998[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jeffrey Mahoney]

Please try this. Your data needs to start in cell A1 where the header "Invoice" is in cell A1

=SUM(IF(IF(MATCH(A2:A23, A2:A23, 0)=(ROW(A2:A23)-ROW(A1)), (ROW(A2:A23)-ROW(A1)), 0)<>0, B2:B23, ""))

 

[István Hirsch]

Try this:

Excel Workbook
	A	B	C	D
1	Ivoice	Totals
2	INV-000014	128	INV-000014	256
3	INV-000014	128	INV-000021	892
4	INV-000021	223	INV-000105	5130
5	INV-000021	223	INV-000110	321
6	INV-000021	223	INV-000117	6184
7	INV-000021	223	INV-000118	2994
8	INV-000105	855
9	INV-000105	855
10	INV-000105	855
11	INV-000105	855
12	INV-000105	855
13	INV-000105	855
14	INV-000110	107
15	INV-000110	107
16	INV-000110	107
17	INV-000117	1546
18	INV-000117	1546
19	INV-000117	1546
20	INV-000117	1546
21	INV-000118	998
22	INV-000118	998
23	INV-000118	998
Sheet1

 

[Jarodjp42]

Please try this. Your data needs to start in cell A1 where the header "Invoice" is in cell A1

=SUM(IF(IF(MATCH(A2:A23, A2:A23, 0)=(ROW(A2:A23)-ROW(A1)), (ROW(A2:A23)-ROW(A1)), 0)<>0, B2:B23, ""))

Thank you for the response, however I am getting an error code when I type in the formula. It is telling me I haven't satisfied the IF logical test. =SUM(IF(IF(MATCH(A2:A23, A2:A23, 0)=(ROW(A2:A23)-ROW(A1)), (ROW(A2:A23)-ROW(A1)), 0)<>0, B2:B23, "")) It puts the cursor between the two bold parentheses. Any thoughts?

 

[Jarodjp42]

Try this:

Sheet1

	A	B	C	D
Ivoice	Totals
INV-000014	INV-000014
INV-000014	INV-000021
INV-000021	INV-000105
INV-000021	INV-000110
INV-000021	INV-000117
INV-000021	INV-000118
INV-000105
INV-000105
INV-000105
INV-000105
INV-000105
INV-000105
INV-000110
INV-000110
INV-000110
INV-000117
INV-000117
INV-000117
INV-000117
INV-000118
INV-000118
INV-000118

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:105px;"><col style="width:64px;"><col style="width:93px;"><col style="width:90px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]128[/TD]

[TD="align: right"]256[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]128[/TD]

[TD="align: right"]892[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]223[/TD]

[TD="align: right"]5130[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]223[/TD]

[TD="align: right"]321[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]223[/TD]

[TD="align: right"]6184[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]223[/TD]

[TD="align: right"]2994[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]855[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]855[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]855[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]855[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]855[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]855[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]107[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]107[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]107[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]1546[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]1546[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: right"]1546[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]1546[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: right"]998[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: right"]998[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="align: right"]998[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 {=INDEX($A$2:$A$23, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$23), 0))} D2 {=SUM(IF(C2=$A$2:$A$23,$B$2:$B$23,0))} C3 {=INDEX($A$2:$A$23, MATCH(0, COUNTIF($C$1:C2, $A$2:$A$23), 0))} D3 {=SUM(IF(C3=$A$2:$A$23,$B$2:$B$23,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you for the response, but I am trying to put the formula into one cell and have it sum the amounts for the invoices.

 

[Jeffrey Mahoney]

Shoot, I forgot to tell you to finish the formula as an array formula Ctrl-Shift-Enter. Do that when you are editing the formula

 

[Jarodjp42]

Thank you!!! I had to format the data in a table, that's why I was getting the error message!

 

[Jarodjp42]

Shoot, I forgot to tell you to finish the formula as an array formula Ctrl-Shift-Enter. Do that when you are editing the formula

Is there any way you can breakdown the formula and explain it to me. I would like to know why and how it works so I can use the functions in future formulas! Thanks!