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

Jarodjp42 · Nov 22, 2017

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 · Nov 22, 2017

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 · Nov 22, 2017

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 · Nov 22, 2017

Jeffrey Mahoney said:
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 · Nov 22, 2017

István Hirsch said:
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 · Nov 22, 2017

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 · Nov 22, 2017

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

Jarodjp42 · Nov 22, 2017

Jeffrey Mahoney said:
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!

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

Jarodjp42

New Member

Excel Facts

Jeffrey Mahoney

Well-known Member

István Hirsch

Well-known Member

Jarodjp42

New Member

Jarodjp42

New Member

Jeffrey Mahoney

Well-known Member

Jarodjp42

New Member

Jarodjp42

New Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock