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

4964542948	201109
4966894994	201107
4930321980	201107
4973587935	201101
4934688031	201107
4964542948	201109
4964542948	201109
4970536953	201107
4966894993	201107
4966894993	201107
4964542948	201109
4973587935	201101
4964542948	201109
4925479054	201107
4978588949	201112
4966894993	201107
4966894993	201107

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Eric W]

Welcome to the forum.

Assuming your transactions are numeric, try:

	A	B	C	D	E
1	Transactions	Period/Yr		Period/Yr	Unique Count
2	4964542948	201109		201107	6
3	4966894994	201107
4	4930321980	201107
5	4973587935	201101
6	4934688031	201107
7	4964542948	201109
8	4964542948	201109
9	4970536953	201107
10	4966894993	201107
11	4966894993	201107
12	4964542948	201109
13	4973587935	201101
14	4964542948	201109
15	4925479054	201107
16	4978588949	201112
17	4966894993	201107
18	4966894993	201107

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet15​

Array Formulas Cell Formula E2 {=SUM(IF(FREQUENCY(IF(B2:B18=D2,A2:A18),A2:A18),1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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!