Counting Column B entries, excluding duplicate criteria in column A.

[jakiechan]

Hello,

I am making an investment spreadsheet and I need to count how many stocks I have a dividend reinvestment plan (DRP) with.

Here is an example of what I am working with:
Sample.png

Stocks that are listed as "Yes" in column B will be counted as 1 dividend reinvestment plan.

I want to only count the same stock ONCE. For example, in this case, Rio Tinto will count as 1 dividend reinvestment plan, even though there are 2 Rio Tinto entries with "Yes."
This is because it is just repeated (I have purchased the same stock multiple times, but I am still recording this listing as being in 1 dividend reinvestment plan because it is the same stock).

I need this formula to count through the entire columns for A and B, not just this table. This is just an example. I would like the total DRP count to display in a single cell.

I have attached a Sample workbook in this thread. Please note that this sample does use live data to display the stock names and was made using the latest version of Excel for Office 365.

Thank you!

[CARACALLA]

	A	B	C	D	E
1	CODE	DRP		COUNT	CODE
2	Apple Inc (XNAS:AAPL)	no		1	Rio Tinto Ltd (XASX:RIO)
3	Microsoft Corp (XNAS:MSFT)	No
4	Tesla Inc (XNAS:TSLA)	No
5	Rio Tinto Ltd (XASX:RIO)	Yes
6	Cochlear Ltd (XASX:COH)	No
7	CSL Ltd (XASX:CSL)	No
8	Apple Inc (XNAS:AAPL)	No
9	Tesla Inc (XNAS:TSLA)	No
10	Rio Tinto Ltd (XASX:RIO)	Yes

D2=SUM(IF(FREQUENCY(IF($B$2:$B$100="Yes",MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1),1))

Control+shift+enter


E2=IFERROR(INDEX($A$2:$A$100,SMALL(IF(FREQUENCY(IF($B$2:$B$100="yes",MATCH($A$2:$A$100,$A$2:$A$100,0)),ROW($A$2:$A$100)-ROW($A$2)+1),ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($A$2:A2))),"")


Control+shift+enter

copy down

[jakiechan]

	A	B	C	D	E
1	CODE	DRP		COUNT	CODE
2	Apple Inc (XNAS:AAPL)	no		1	Rio Tinto Ltd (XASX:RIO)
3	Microsoft Corp (XNAS:MSFT)	No
4	Tesla Inc (XNAS:TSLA)	No
5	Rio Tinto Ltd (XASX:RIO)	Yes
6	Cochlear Ltd (XASX:COH)	No
7	CSL Ltd (XASX:CSL)	No
8	Apple Inc (XNAS:AAPL)	No
9	Tesla Inc (XNAS:TSLA)	No
10	Rio Tinto Ltd (XASX:RIO)	Yes

D2=SUM(IF(FREQUENCY(IF($B$2:$B$100="Yes",MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1),1))

Control+shift+enter


E2=IFERROR(INDEX($A$2:$A$100,SMALL(IF(FREQUENCY(IF($B$2:$B$100="yes",MATCH($A$2:$A$100,$A$2:$A$100,0)),ROW($A$2:$A$100)-ROW($A$2)+1),ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($A$2:A2))),"")


Control+shift+enter

copy down

This works!! You are amazing, thank you!!