Sum two columns, duplicates count as one

[NHagedorn]

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

Thank you,
Nate

[TABLE="class: grid, width: 320, align: center"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"] [/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Total=7[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Assuming your values are either 0 or 1 for both columns:

Excel Workbook
	A	B	C	D
1	1	0		7
2	1	0
3	1	1
4	0	1
5	0	1
6	0	0
7	1	0
8	1	1
Sheet1

 

[NHagedorn]

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))



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD] E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]red[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]blue[/TD]
[TD="align: right"][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]red[/TD]
[TD][/TD]
[TD] 2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[NHagedorn]

=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?