Count with multiple criteria excluding duplciates

[ringws]

Hello,

I am struggling trying to get a count formula to count a number of transactions (with duplicate numbers) only once within a date range in a table containing over 60 months. In the example below, I need to count the number of transactions (which has duplicates and I need them to be counted as one) by the period/yr. i.e. how many transactions for 201107?

I cant do a pivot table or the remove duplicates function, I need a formula. Thank you.

Example Table:
Transactions Period/Yr
[TABLE="width: 141"]
<tbody>[TR]
[TD="align: right"]4964542948[/TD]
[TD="align: right"] 201109[/TD]
[/TR]
[TR]
[TD="align: right"]4966894994[/TD]
[TD="align: right"]201107[/TD]
[/TR]
[TR]
[TD="align: right"]4930321980[/TD]
[TD="align: right"]201107[/TD]
[/TR]
[TR]
[TD="align: right"]4973587935[/TD]
[TD="align: right"]201101[/TD]
[/TR]
[TR]
[TD="align: right"]4934688031[/TD]
[TD="align: right"]201107[/TD]
[/TR]
[TR]
[TD="align: right"]4964542948[/TD]
[TD="align: right"]201109[/TD]
[/TR]
[TR]
[TD="align: right"]4964542948[/TD]
[TD="align: right"]201109[/TD]
[/TR]
[TR]
[TD="align: right"]4970536953[/TD]
[TD="align: right"]201107[/TD]
[/TR]
[TR]
[TD="align: right"]4966894993[/TD]
[TD="align: right"]201107[/TD]
[/TR]
[TR]
[TD="align: right"]4966894993[/TD]
[TD="align: right"]201107[/TD]
[/TR]
[TR]
[TD="align: right"]4964542948[/TD]
[TD="align: right"]201109[/TD]
[/TR]
[TR]
[TD="align: right"]4973587935[/TD]
[TD="align: right"]201101[/TD]
[/TR]
[TR]
[TD="align: right"]4964542948[/TD]
[TD="align: right"]201109[/TD]
[/TR]
[TR]
[TD="align: right"]4925479054[/TD]
[TD="align: right"]201107[/TD]
[/TR]
[TR]
[TD="align: right"]4978588949[/TD]
[TD="align: right"]201112[/TD]
[/TR]
[TR]
[TD="align: right"]4966894993[/TD]
[TD="align: right"]201107[/TD]
[/TR]
[TR]
[TD="align: right"]4966894993[/TD]
[TD="align: right"]201107[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Eric W]

Welcome to the forum.

Assuming your transactions are numeric, try:

	A	B	C	D	E
Transactions	Period/Yr	Period/Yr	Unique Count

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]
[TD="align: right"]4964542948[/TD]
[TD="align: right"]201109[/TD]
[TD="align: right"][/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4966894994[/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4930321980[/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4973587935[/TD]
[TD="align: right"]201101[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4934688031[/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4964542948[/TD]
[TD="align: right"]201109[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4964542948[/TD]
[TD="align: right"]201109[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4970536953[/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]4966894993[/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]4966894993[/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: center"]14[/TD]
[TD="align: right"]4964542948[/TD]
[TD="align: right"]201109[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]4925479054[/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]4978588949[/TD]
[TD="align: right"]201112[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]4966894993[/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]4966894993[/TD]
[TD="align: right"]201107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(B2:B18=D2,A2:A18),A2:A18),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[ringws]

Thanks Eric, that did work like a charm! I should have asked hours ago! Thank you for your help. I had not been trying the {} trick.

 

[Eric W]

Glad to help!