Sumif no duplicates

B

bradyman97

Board Regular
Joined
Feb 22, 2008
Messages
60
Office Version
  1. 2019
IÂ’m looking for a formula that counts how many A, B, C, etc. there are but if there is a duplicate value in column B it only counts it as one.

Title should be countif

[TABLE="width: 100"]
<tbody>[TR]
[TD]A[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]


So it will look like this

[TABLE="width: 100"]
<tbody>[TR]
[TD]A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your time
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hey there, hope this helps:

CD

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]100[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]200[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]300[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]200[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]300[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]200[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]400[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]200[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]100[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]300[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="align: right"]2[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D14[/TH]
[TD="align: left"]=SUMPRODUCT(((($C$2:$C$11=C14))/COUNTIFS($C$2:$C$11,$C$2:$C$11&"",$D$2:$D$11,$D$2:$D$11&"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D15[/TH]
[TD="align: left"]=SUMPRODUCT(((($C$2:$C$11=C15))/COUNTIFS($C$2:$C$11,$C$2:$C$11&"",$D$2:$D$11,$D$2:$D$11&"")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D16[/TH]
[TD="align: left"]=SUMPRODUCT(((($C$2:$C$11=C16))/COUNTIFS($C$2:$C$11,$C$2:$C$11&"",$D$2:$D$11,$D$2:$D$11&"")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
another option with PowerQuery aka Get&Transform

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Item"}, {{"Count", each Table.RowCount(_), type number}, {"Sum", each List.Sum([Number]), type number}})
in
    #"Grouped Rows"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Item[/td][td=bgcolor:#5B9BD5]Number[/td][td][/td][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Count[/td][td=bgcolor:#70AD47]Sum[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]
100​
[/td][td][/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
300​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]
200​
[/td][td][/td][td]B[/td][td]
2​
[/td][td]
500​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7]
300​
[/td][td][/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
600​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]C[/td][td]
200​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7]
300​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]
200​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]C[/td][td=bgcolor:#DDEBF7]
400​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]C[/td][td]
200​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]
100​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]
300​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
Here is another option.
It's an array formula that must be entered with CTRL-SHIFT-ENTER. Drag formula down as needed.
If you have a large data set this will probably be a little faster than SUMPRODUCT.
Excel Workbook
AB
1A100
2A200
3B300
4C200
5B300
6B200
7C400
8C200
9A100
10B300
11
12
13A2
14B2
15C2
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
297
Peter_SSs
Peter_SSs
G
  • Question Question
How to loop through data to generate charts
Replies
7
Views
120
HighAndWilder
H
J
  • Solved
Subtotals within Sumifs range
Replies
6
Views
978
jaz44
J
M
  • Solved
add multiples message boxes to procedure if exceed the QTY in textbox on userform
Replies
7
Views
340
dmt32
D
A
  • Solved
Less intensive alternative to FILTER function?
Replies
3
Views
173
Domenic
D

Forum statistics

Threads
1,223,952
Messages
6,175,594
Members
452,656
Latest member
earth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top