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:

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]DATE
[/TD]
[TD]# OF CUSTOMERS
[/TD]
[/TR]
[TR]
[TD]JAN-16
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]FEB-16
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]MAR-16
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]


Starting from a table that looks similar to this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]DATE
[/TD]
[TD]CUSTOMER NAME
[/TD]
[/TR]
[TR]
[TD]JAN-16
[/TD]
[TD]CUSTOMER_NAME_01
[/TD]
[/TR]
[TR]
[TD]JAN-16
[/TD]
[TD]CUSTOMER_NAME_01
[/TD]
[/TR]
[TR]
[TD]JAN-16
[/TD]
[TD]CUSTOMER_NAME_02
[/TD]
[/TR]
[TR]
[TD]JAN-16
[/TD]
[TD]CUSTOMER_NAME_03
[/TD]
[/TR]
[TR]
[TD]FEB-16
[/TD]
[TD]CUSTOMER_NAME_01
[/TD]
[/TR]
[TR]
[TD]FEB-16
[/TD]
[TD]CUSTOMER_NAME_02
[/TD]
[/TR]
[TR]
[TD]FEB-16
[/TD]
[TD]CUSTOMER_NAME_02
[/TD]
[/TR]
[TR]
[TD]MAR-16
[/TD]
[TD]CUSTOMER_NAME_01
[/TD]
[/TR]
[TR]
[TD]MAR-16
[/TD]
[TD]CUSTOMER_NAME_01
[/TD]
[/TR]
[TR]
[TD]MAR-16
[/TD]
[TD]CUSTOMER_NAME_03
[/TD]
[/TR]
[TR]
[TD]MAR-16
[/TD]
[TD]CUSTOMER_NAME_03
[/TD]
[/TR]
</tbody>[/TABLE]
</h15)*(date>

 

[FranzV]

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

	A	B
DATE	CUSTOMER COUNT
JAN-16
FEB-16
MAR-16
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

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

[TD="align: center"]2[/TD]

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

[TD="align: center"]3[/TD]

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

[TD="align: center"]4[/TD]

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

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

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

</tbody>

​

[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"]B2[/TH]
[TD]{=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))}[/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]

 

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


[TABLE="class: cms_table, width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: cms_table, width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5, align: center"]B2[/TH]
[TD]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]DATE[/TD]
[TD]CUSTOMER COUNT[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]JAN-16[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]FEB-16[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]MAR-16[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]DATE[/TD]
[TD]CUSTOMER_NAME[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]JAN-16[/TD]
[TD]CUSTOMER_NAME_01[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]JAN-16[/TD]
[TD]CUSTOMER_NAME_01[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]JAN-16[/TD]
[TD]CUSTOMER_NAME_02[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]JAN-16[/TD]
[TD]CUSTOMER_NAME_02[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]FEB-16[/TD]
[TD]CUSTOMER_NAME_01[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]FEB-16[/TD]
[TD]CUSTOMER_NAME_02[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]FEB-16[/TD]
[TD]CUSTOMER_NAME_02[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]MAR-16[/TD]
[TD]CUSTOMER_NAME_01[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]MAR-16[/TD]
[TD]CUSTOMER_NAME_01[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]MAR-16[/TD]
[TD]CUSTOMER_NAME_03[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]MAR-16[/TD]
[TD]CUSTOMER_NAME_03[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] DATE[/td][td] CUSTOMER NAME[/td][td][/td][td] DATE[/td][td] # OF CUSTOMERS[/td][/tr]
[tr][td]

2​

[/td][td] Jan-16[/td][td] CUSTOMER_NAME_01[/td][td][/td][td] Jan-16[/td][td] 3[/td][/tr]
[tr][td]

3​

[/td][td] Jan-16[/td][td] CUSTOMER_NAME_01[/td][td][/td][td] Feb-16[/td][td] 2[/td][/tr]
[tr][td]

4​

[/td][td] Jan-16[/td][td] CUSTOMER_NAME_02[/td][td][/td][td] Mar-16[/td][td] 2[/td][/tr]
[tr][td]

5​

[/td][td] Jan-16[/td][td] CUSTOMER_NAME_03[/td][td][/td][td][/td][td] [/td][/tr]
[tr][td]

6​

[/td][td] Feb-16[/td][td] CUSTOMER_NAME_01[/td][td][/td][td][/td][td] [/td][/tr]
[tr][td]

7​

[/td][td] Feb-16[/td][td] CUSTOMER_NAME_02[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td] Feb-16[/td][td] CUSTOMER_NAME_02[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td] Mar-16[/td][td] CUSTOMER_NAME_01[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td] Mar-16[/td][td] CUSTOMER_NAME_01[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td] Mar-16[/td][td] CUSTOMER_NAME_03[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td] Mar-16[/td][td] CUSTOMER_NAME_03[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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)))