Counting Data "Sold" Ignore Duplicate Number

muhammad susanto · Jun 13, 2019

hi all.. how to counting data only "sold" but ignore repeate or duplicate "number document " like this : <style type="text/css"> table.tableizer-table { font-size: 12px; border: 1px solid #CCC ; font-family: Arial, Helvetica, sans-serif; } .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #CCC ; } .tableizer-table th { background-color: #104E8B ; color: #FFF ; font-weight: bold; } </style> [TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Number Doc[/TH]
[TH]Status[/TH]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Sold
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sold[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sold
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Sold[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sold[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Not Sold[/TD]
[/TR]
</tbody>[/TABLE]

my expected result for count of "sold" is 3
thanks in advance sst

levy77 · Jun 13, 2019

Hi

Does this help?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]Sold[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sold[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sold[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sold[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sold[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Not Sold[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL COUNT[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Formula to go in cell C1 and drag down =IF(COUNTIF($A$1:A1,A1)=1,1,0)

Formula to go in cell C7 =SUMIF(B1:B6,"Sold",C1:C6)

muhammad susanto · Jun 13, 2019

hi levy77..thanks working well...but can your formula in single column/row? without helped column

muhammad susanto · Jun 13, 2019

hi..i think there is wrong if position data defferent like this :
[TABLE="class: cms_table_tableizer-table"]
<tbody>[TR="class: cms_table_tableizer-firstrow"]
[TH]umber Doc[/TH]
[TH]Status[/TH]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Sold
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sold[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sold[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Not Sold
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Not Sold
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Sold
[/TD]
[/TR]
</tbody>[/TABLE]
the formula not work properly

kweaver · Jun 13, 2019

Assuming your data has column headers and the real data is in A2:B7, try this array formula in C2: CTRL+SHIFT+Enter

Code:

=SUM(--(FREQUENCY(IF(B2:B7="Sold",MATCH(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)+1)>0))

Excel 2010

A

B

C

2

1

Sold

3

2

Sold

4

2

Sold

5

3

Sold

6

3

Sold

7

4

Not sold

Sheet3

Cell Formulas
Range	Formula
C2	{=SUM(--(FREQUENCY(IF(B2:B7="Sold",MATCH(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)+1)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Counting Data "Sold" Ignore Duplicate Number

muhammad susanto

Well-known Member

Excel Facts

levy77

Board Regular

muhammad susanto

Well-known Member

muhammad susanto

Well-known Member

kweaver

Well-known Member

Similar threads

Forum statistics

Share this page

Counting Data "Sold" Ignore Duplicate Number

muhammad susanto

Well-known Member

Excel Facts

levy77

Board Regular

muhammad susanto

Well-known Member

muhammad susanto

Well-known Member

kweaver

Well-known 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