formula to count unique values based on today's date

[p4nny]

Hi

I'm column B, i have a list of values (made of letter & numbers). In column K I have the date

column B e.g.

151_92861310053100</SPAN>

039_6B7453EFADA0B0FFF1986A56E61F20FC</SPAN>

151_92861310053100</SPAN>

151_92861310282700</SPAN>

151_92861310282700</SPAN>

151_92861310282700</SPAN>

<TBODY>
</TBODY>

I would like a formula to provide a unique count of B and filter for "today" on column C

My range will go from B13:B4000 & K13:K4000 and will include blank cells

Appreciate the help​

 

[Domenic]

To count the unique values in Column B where the corresponding value in Column K equals today's date, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(K13:K4000=TODAY(),IF(LEN(B13:B4000)>0,MATCH("~"&B13:B4000,B13:B4000&"",0))),ROW(B13:B4000)-ROW(B13)+1)>0,1))

Hope this helps!

 

[p4nny]

Works perfect, many thanks

Would you mind explaining how the formula works?

 

[Domenic]

As you know, FREQUENCY calculates the number of times values occur within a range of values, and returns a vertical array of numbers. And, as you'll also know, FREQUENCY has two arguments, data_array and bins_array.

In our formula, the IF() function within FREQUENCY returns the array of values used as the data_array, and ROW()-ROW()+1 returns the array of values used as the bins_array.

Now, let's assume that B13:B4000 and K13:K4000 contain the following data...

A									11/08/15
B									11/12/15
C									11/09/15
B									11/12/15
D									11/12/15
etc									etc

<tbody>
</tbody>

For the data_array, here's how the IF() function is evaluated...

IF(K13:K4000=TODAY(),IF(LEN(B13:B4000)>0,MATCH("~"&B13:B4000,B13:B4000&"",0)))

IF({FALSE;TRUE;FALSE;TRUE;TRUE; ... },IF({TRUE;TRUE;TRUE;TRUE;TRUE; ... },{1;2;3;2;5}))

{FALSE;2;FALSE;2;5; ... }

So here we have MATCH return the relative position of each value in B13:B4000 within the range B13:B4000, where the cell in B13:B4000 isn't blank, and where the corresponding value in K13:K4000 is equal to today's date. Otherwise FALSE is returned.

Note that MATCH considers the asterisk (*) and question mark (?) wildcards. So "~" is prepended to each lookup value in B13:B4000. It's an escape character and ensures that the first character of the cell is treated as a regular character instead of a wildcard, in case the first character is an asterisk or question mark. In doing so, though, it converts each value into a text value, if not already so. Hence we append "" to each value in the lookup array B13:B4000 to ensure each one is also a text value.

For the bins_array, here's how ROW()-ROW()+1 is evaluated...

ROW(B13:B4000)-ROW(B13)+1)

{13;14;15;16;17; ... }-{13}+1

{0;1;2;3;4; ... }+1

{1;2;3;4;5; ... }

And so here's how the rest of the formula is evaluated...

=SUM(IF(FREQUENCY({FALSE;2;FALSE;2;5; ... },{1;2;3;4;5; ... })>0,1))

=SUM(IF({0;2;0;0;1; ... }>0,1))

=SUM({FALSE;1;FALSE;FALSE;1; ... })

...and then the array of values is passed to the SUM function, which returns the sum.

Hope this helps!

 

[WikusOlivier]

Hi there,

please help. I am to inexperienced to get this done and I have been struggling for days.

I need to count the unique values in Column A by checking their dates and comparing them with the month in Column C.

In the real sheet my rows will go up to 10000 and I'll track the monthly apps for the next 5 years.

I really hope you can help.

Unqiue No	DAte	Month	Total Apps
001	21/06/2018	June-2018	2
002	23/08/2018	July-2018	0
003	15/06/2018	August-2018	1
001	21/06/2018
002	23/08/2018

<tbody>
</tbody>

As you know, FREQUENCY calculates the number of times values occur within a range of values, and returns a vertical array of numbers. And, as you'll also know, FREQUENCY has two arguments, data_array and bins_array.

In our formula, the IF() function within FREQUENCY returns the array of values used as the data_array, and ROW()-ROW()+1 returns the array of values used as the bins_array.

Now, let's assume that B13:B4000 and K13:K4000 contain the following data...

A									11/08/15
B									11/12/15
C									11/09/15
B									11/12/15
D									11/12/15
etc									etc

<tbody>
</tbody>

For the data_array, here's how the IF() function is evaluated...

IF(K13:K4000=TODAY(),IF(LEN(B13:B4000)>0,MATCH("~"&B13:B4000,B13:B4000&"",0)))

IF({FALSE;TRUE;FALSE;TRUE;TRUE; ... },IF({TRUE;TRUE;TRUE;TRUE;TRUE; ... },{1;2;3;2;5}))

{FALSE;2;FALSE;2;5; ... }

So here we have MATCH return the relative position of each value in B13:B4000 within the range B13:B4000, where the cell in B13:B4000 isn't blank, and where the corresponding value in K13:K4000 is equal to today's date. Otherwise FALSE is returned.

Note that MATCH considers the asterisk (*) and question mark (?) wildcards. So "~" is prepended to each lookup value in B13:B4000. It's an escape character and ensures that the first character of the cell is treated as a regular character instead of a wildcard, in case the first character is an asterisk or question mark. In doing so, though, it converts each value into a text value, if not already so. Hence we append "" to each value in the lookup array B13:B4000 to ensure each one is also a text value.

For the bins_array, here's how ROW()-ROW()+1 is evaluated...

ROW(B13:B4000)-ROW(B13)+1)

{13;14;15;16;17; ... }-{13}+1

{0;1;2;3;4; ... }+1

{1;2;3;4;5; ... }

And so here's how the rest of the formula is evaluated...

=SUM(IF(FREQUENCY({FALSE;2;FALSE;2;5; ... },{1;2;3;4;5; ... })>0,1))

=SUM(IF({0;2;0;0;1; ... }>0,1))

=SUM({FALSE;1;FALSE;FALSE;1; ... })

...and then the array of values is passed to the SUM function, which returns the sum.

Hope this helps!

 

[Aladin Akyurek]

@ WikusOlivier

Are the Unique No values true numbers or text numbers?

 

[WikusOlivier]

Hi. They are Text. A typical Unique No will look like this "DR00056/11062018"

@ WikusOlivier

Are the Unique No values true numbers or text numbers?

 

[Aladin Akyurek]

Hi. They are Text. A typical Unique No will look like this "DR00056/11062018"

Thanks.

Book1
	A	B	C	D
1	Unqiue No	Date	Month	Total Apps
2	001	6/21/2018	Jun-18	2
3	002	8/23/2018	Jul-18	0
4	003	6/15/2018	Aug-18	1
5	001	6/21/2018
6	002	8/23/2018
Sheet1

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

=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6-DAY($B$2:$B$6)+1=$C2,MATCH($A$2:$A$6,$A$2:$A$6,0))),ROW($A$2:$A$6)-ROW($A$2)+1),1))

Note. Since the Unique No values do not appear to contain special meaning characters, MATCH($A$2:$A$6,$A$2:$A$6,0) is used instead of MATCH("~"$A$2:$A$6,$A$2:$A$6&"",0) in the foregoing formula.

 

[WikusOlivier]

That works perfectly.

Thank you so much. I'll spend some time to try and understand how the formula works.

Have a great day. Thank you!

Thanks.

	A	B	C	D
1	Unqiue No	Date	Month	Total Apps
2	001	6/21/2018	Jun-18	2
3	002	8/23/2018	Jul-18	0
4	003	6/15/2018	Aug-18	1
5	001	6/21/2018
6	002	8/23/2018

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

Sheet1​

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

=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6-DAY($B$2:$B$6)+1=$C2,MATCH($A$2:$A$6,$A$2:$A$6,0))),ROW($A$2:$A$6)-ROW($A$2)+1),1))

Note. Since the Unique No values do not appear to contain special meaning characters, MATCH($A$2:$A$6,$A$2:$A$6,0) is used instead of MATCH("~"$A$2:$A$6,$A$2:$A$6&"",0) in the foregoing formula.

 

[Aladin Akyurek]

That works perfectly.

Thank you so much. I'll spend some time to try and understand how the formula works.

Have a great day. Thank you!

You are welcome.

Here is an explanation link:

https://www.mrexcel.com/forum/excel-questions/633564-counting-duplicates-using-sum-if-frequency-match-2.html#post3156949