Unique count of values in column per month

0
votes

Excel-Table:

     |       A        |         B       |       C          |    D   |   E     |     F   |     G   |
-----|----------------|-----------------|------------------|--------|---------|---------|---------|-----
1    |   month&year   |      date       |     customer     |        | 2020-01 | 2020-03 | 2020-04 |
-----|----------------|-----------------|------------------|--------|---------|---------|---------|-----
2    |     2020-01    |    2020-01-10   |    Customer A    |        |    3    |    2    |    4    |
3    |     2020-01    |    2020-01-14   |    Customer A    |        |         |         |         |
4    |     2020-01    |    2020-01-17   |    Customer B    |        |         |         |         | 
5    |     2020-01    |    2020-01-19   |    Customer B    |        |         |         |         | 
6    |     2020-01    |    2020-01-23   |    Customer C    |        |         |         |         | 
7    |     2020-01    |    2020-01-23   |    Customer B    |        |         |         |         | 
-----|----------------|-----------------|---------------- -|--------|---------|---------|---------|-----
8    |     2020-03    |    2020-03-18   |    Customer E    |        |         |         |         | 
9    |     2020-03    |    2020-03-19   |    Customer A    |        |         |         |         | 
-----|----------------|-----------------|------------------|--------|---------|---------|---------|-----
10   |     2020-04    |    2020-04-04   |    Customer B    |        |         |         |         | 
11   |     2020-04    |    2020-04-07   |    Customer C    |        |         |         |         | 
12   |     2020-04    |    2020-04-07   |    Customer A    |        |         |         |         | 
13   |     2020-04    |    2020-04-07   |    Customer E    |        |         |         |         | 
14   |     2020-04    |    2020-04-08   |    Customer A    |        |         |         |         | 
15   |     2020-04    |    2020-04-12   |    Customer A    |        |         |         |         | 
16   |     2020-04    |    2020-04-15   |    Customer B    |        |         |         |         | 
17   |                |

In my Excel file I want to calculate the unique count of cutomers per month as you can see in Cell E2:G2.

I already inserted Column A as a helper column which extracts only the month and the year from the date in Column B.
Therefore, the date-formatting is the same as in the timline in Cell E1:G2.

I guess the formula to get the unique count per month is somehow related to =COUNTIFS($A:$A,E$1) but I have no clue how to modify this formula to get the expected values.

Do you have any idea?

2
excel
You can create a pivot table where you take your "month&year" as rowlabels and use "customer" as values. When initializing the pivot-table, choose to add the data to the data model. You can then set the values to show the unique count. – JvdV
What version of Excel do you have? – shrivallabha.redij
Office365. However, if you also have solution for Excel 2016 would be great. – Michi

2 Answers

1
votes

Here's one approach which would work for Office 365 and if you have access to UNIQUE:

=COUNTA(UNIQUE(IF($A$2:$A$16=G$1,$C$2:$C$16,""),,FALSE))-1

For older versions, following will work with CTRL+SHIFT+ENTER (array entry)

=SUM(--(FREQUENCY(IFERROR(MATCH($A$2:$A$16&$C$2:$C$16,E$1&$C$2:$C$16,0),"a"),MATCH($A$2:$A$16&$C$2:$C$16,E$1&$C$2:$C$16,0))>0))

0
votes

You can do it without any helping column.

=SUM(--(UNIQUE(FILTER($C$2:$C$16,TEXT($B$2:$B$16,"yyyy-mm")=E$1))<>""))

For older version of excel use below formula with your helper column.

=SUMPRODUCT(--($A$2:$A$16=D$1)*(1/COUNTIFS($A$2:$A$16,$A$2:$A$16,$C$2:$C$16,$C$2:$C$16)))

enter image description here