Count unique instances of text between two dates

[B5rocksass]

Please note that my skills are few and far between. I have tried the following formula with CTRL+SHIFT+ENTER but it returns zero. I need to find the unique # of customers that purchased material each month. Here is the formula that failed: =SUMPRODUCT(IF((Date<h15)*(date>=H14),1/COUNTIFS(Customer_Name,"<="&H15,Date,">="&H14,Customer_Name,Customer_Name),0))

I would like to put this in a table that shows the calendar year, Jan-16 to Jan -17. So it looks like:

DATE	# OF CUSTOMERS
JAN-16	3
FEB-16	2
MAR-16	2

<tbody>
</tbody>

Starting from a table that looks similar to this:

DATE	CUSTOMER NAME
JAN-16	CUSTOMER_NAME_01
JAN-16	CUSTOMER_NAME_01
JAN-16	CUSTOMER_NAME_02
JAN-16	CUSTOMER_NAME_03
FEB-16	CUSTOMER_NAME_01
FEB-16	CUSTOMER_NAME_02
FEB-16	CUSTOMER_NAME_02
MAR-16	CUSTOMER_NAME_01
MAR-16	CUSTOMER_NAME_01
MAR-16	CUSTOMER_NAME_03
MAR-16	CUSTOMER_NAME_03

<tbody>
</tbody>

</h15)*(date>

 

[FranzV]

Using a formula provided by Exceljet, you can do the following:

	A	B
1	DATE	CUSTOMER COUNT
2	JAN-16	3
3	FEB-16	2
4	MAR-16	2
5
6	DATE	CUSTOMER_NAME
7	JAN-16	CUSTOMER_NAME_01
8	JAN-16	CUSTOMER_NAME_01
9	JAN-16	CUSTOMER_NAME_02
10	JAN-16	CUSTOMER_NAME_03
11	FEB-16	CUSTOMER_NAME_01
12	FEB-16	CUSTOMER_NAME_02
13	FEB-16	CUSTOMER_NAME_02
14	MAR-16	CUSTOMER_NAME_01
15	MAR-16	CUSTOMER_NAME_01
16	MAR-16	CUSTOMER_NAME_03
17	MAR-16	CUSTOMER_NAME_03

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

​

Array Formulas Cell Formula B2 {=SUM(IF(FREQUENCY(IF(BigTable[DATE]=[@DATE], MATCH(BigTable[CUSTOMER_NAME],BigTable[CUSTOMER_NAME],0)),ROW(BigTable[CUSTOMER_NAME])-ROW(BigTable[[#Headers],[CUSTOMER_NAME]])),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>

 

[B5rocksass]

Hi FranzV,

In the formula you have above, I see two questionable bits of data: BigTable and #Headers. I assumed BigTable is the columns of the longer version of the list above, so I used A:B and for # headers in either the first table or the resulting data compilation table I used 2. Then I made it an array, but I got an error. When I hit ok, the first instance of B[DATE] was highlighted in the formula string. Am I doing something wrong?

{=SUM(IF(FREQUENCY(IF(A:B[DATE]=[@DATE], MATCH(A:B[CUSTOMER_NAME],A:B[CUSTOMER_NAME],0)),ROW(A:B[CUSTOMER_NAME])-ROW(A:B[[2],[CUSTOMER_NAME]])),1))}

 

[FranzV]

For the dummy table I created I applied Excel's built-in Table Formatting by selecting a cell within the table and pressing CTRL+T. This makes formulas much easier to read. I encourage you to try it. That said, I have changed the references to normal A1 Style in the formula below. I hope the colors make it easier to understand.

Array Formulas Cell Formula B2 {=SUM(IF(FREQUENCY(IF($A$7:$A:$17=$A2, MATCH($B$7:$B$17,$B$7:$B$17,0)),ROW($B$7:$B$17)-ROW($B$6)),1))} <tbody> </tbody>

<tbody>
</tbody>

	A	B
1	DATE	CUSTOMER COUNT
2	JAN-16	3
3	FEB-16	2
4	MAR-16	2
5
6	DATE	CUSTOMER_NAME
7	JAN-16	CUSTOMER_NAME_01
8	JAN-16	CUSTOMER_NAME_01
9	JAN-16	CUSTOMER_NAME_02
10	JAN-16	CUSTOMER_NAME_02
11	FEB-16	CUSTOMER_NAME_01
12	FEB-16	CUSTOMER_NAME_02
13	FEB-16	CUSTOMER_NAME_02
14	MAR-16	CUSTOMER_NAME_01
15	MAR-16	CUSTOMER_NAME_01
16	MAR-16	CUSTOMER_NAME_03
17	MAR-16	CUSTOMER_NAME_03

<tbody>
</tbody>

 

[Caribeiro77]

Shouldn't the result of Jan-16 be 2?

 

[FranzV]

Shouldn't the result of Jan-16 be 2?

There is a typo in cell B10. The original table read CUSTOMER_NAME_03. My bad. Thanks for pointing it out.

 

[B5rocksass]

Running into a problem because the actual document is almost 6,000 rows long and is expected to get longer as we go through the year. When I tried to input the formula as you've described, I got a different error saying "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated."

So I copied the table to a different document and deleted all but 200 rows. Got the same error. I updated the formula to include the entire column because it is expected to grow
=SUM(IF(FREQUENCY(IF(A:A=G2,MATCH(B:B,0)),ROW(B:B)-ROW($B$2)),1))

 

[FranzV]

So I copied the table to a different document and deleted all but 200 rows. Got the same error. I updated the formula to include the entire column because it is expected to grow
=SUM(IF(FREQUENCY(IF(A:A=G2,MATCH(B:B,0)),ROW(B:B)-ROW($B$2)),1))

By using references to entire columns you are forcing Excel to do millions of unnecessary calculations. Try using CTRL+T on any cell of your 6,000 row table and using structured references. Any new rows added at the bottom of the table will be included in the calculations automatically.

 

[B5rocksass]

This document is meant to go to someone that knows even less than I do. She won't know how to run this formula every time. Is there another way that doesn't require having to update the formula each time the report is required?

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​
1​	DATE	CUSTOMER NAME		DATE	# OF CUSTOMERS
2​	Jan-16	CUSTOMER_NAME_01		Jan-16	3
3​	Jan-16	CUSTOMER_NAME_01		Feb-16	2
4​	Jan-16	CUSTOMER_NAME_02		Mar-16	2
5​	Jan-16	CUSTOMER_NAME_03
6​	Feb-16	CUSTOMER_NAME_01
7​	Feb-16	CUSTOMER_NAME_02
8​	Feb-16	CUSTOMER_NAME_02
9​	Mar-16	CUSTOMER_NAME_01
10​	Mar-16	CUSTOMER_NAME_01
11​	Mar-16	CUSTOMER_NAME_03
12​	Mar-16	CUSTOMER_NAME_03

In E2 control+shift+enter, not just enter, and copy down:

=IF($D2="","",SUM(IF(FREQUENCY(IF(1-($B$2:$B$12=""),IF($A$2:$A$12=$D2,MATCH($B$2:$B$12,$B$2:$B$12,0))),ROW($B$2:$B$12)-ROW($B$2)+1),1)))