# Sum two columns, duplicates count as one

#### NHagedorn

##### New Member
How do I sum two columns counting duplicates between the two columns as one? Is this a countif?

Thank you,
Nate

 A B C D 1 A B 2 Total=7 1 0 3 1 0 4 1 1 5 0 1 6 0 1 7 0 0 8 1 0 9 1 1

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### JoeMo

##### MrExcel MVP
Assuming your values are either 0 or 1 for both columns:
Excel Workbook
ABCD
1107
210
311
401
501
600
710
811
Sheet1

#### NHagedorn

##### New Member
Awesome. Works great. What if I wanted to add an additional criteria? Red

Thought I could figure it out, but no luck.

Thank you,
Nate

CellFormula D1=SUM(A1:B8)-SUMPRODUCT((A1:A8=1)*(B1:B8=1))

 A B C D E F G H I 1 1 0 red 7 blue 5 2 1 0 red red 2 3 1 1 blue 4 0 1 red 5 0 1 red 6 0 0 blue 7 1 0 blue 8 1 1 red

<tbody>
</tbody>

Last edited:

#### NHagedorn

##### New Member
=SUMIF(C:C,"Red",A:A)+SUMIF(C:C, "Red",B:B)-SUMPRODUCT((A:A=1)*(B:B=1)*(C:C="Red"))

Better, Cleaner way?

Replies
7
Views
84
Replies
45
Views
408
Replies
8
Views
92
Replies
1
Views
18
Replies
2
Views
39

1,109,435
Messages
5,528,746
Members
409,833
Latest member
tdnhan

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...