formula to count unique values based on today's date

p4nny · Nov 9, 2015

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.
[TABLE="class: cms_table"]
<TBODY>[TR]
[TD]151_92861310053100</SPAN>[/TD]
[/TR]
[TR]
[TD]039_6B7453EFADA0B0FFF1986A56E61F20FC</SPAN>[/TD]
[/TR]
[TR]
[TD]151_92861310053100</SPAN>[/TD]
[/TR]
[TR]
[TD]151_92861310282700</SPAN>[/TD]
[/TR]
[TR]
[TD]151_92861310282700</SPAN>[/TD]
[/TR]
[TR]
[TD]151_92861310282700</SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]

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 · Nov 9, 2015

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 · Nov 11, 2015

Works perfect, many thanks

Would you mind explaining how the formula works?

Domenic · Nov 12, 2015

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

[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64, align: right"]11/08/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]11/12/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]11/09/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]11/12/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]11/12/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]etc[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]etc[/TD]
[/TR]
</tbody>[/TABLE]

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

Code:

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

Code:

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

Code:

=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 · Jun 20, 2018

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unqiue No[/TD]
[TD]DAte[/TD]
[TD]Month[/TD]
[TD]Total Apps[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]21/06/2018[/TD]
[TD]June-2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]23/08/2018[/TD]
[TD]July-2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]15/06/2018[/TD]
[TD]August-2018[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]21/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]23/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Domenic said:
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...

[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64, align: right"]11/08/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]11/12/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]11/09/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]11/12/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]11/12/15[/TD]
[/TR]
[TR]
[TD="class: xl65"]etc[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]etc[/TD]
[/TR]
</tbody>[/TABLE]

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

Code:

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

Code:

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

Code:

=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 · Jun 20, 2018

@ WikusOlivier

Are the Unique No values true numbers or text numbers?

WikusOlivier · Jun 20, 2018

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

Aladin Akyurek said:
@ WikusOlivier

Are the Unique No values true numbers or text numbers?

Aladin Akyurek · Jun 20, 2018

WikusOlivier said:
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 · Jun 20, 2018

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!

Aladin Akyurek said:
Thanks.

A B C D
Unqiue No Date Month Total Apps

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

[TD="align: center"]2[/TD]
[TD="align: right"]001[/TD]
[TD="align: right"]6/21/2018[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]002[/TD]
[TD="align: right"]8/23/2018[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]003[/TD]
[TD="align: right"]6/15/2018[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]001[/TD]
[TD="align: right"]6/21/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]002[/TD]
[TD="align: right"]8/23/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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 · Jun 20, 2018

WikusOlivier said:
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

formula to count unique values based on today's date

p4nny

Board Regular

Domenic

MrExcel MVP

p4nny

Board Regular

Domenic

MrExcel MVP

WikusOlivier

New Member

Aladin Akyurek

MrExcel MVP

WikusOlivier

New Member

Aladin Akyurek

MrExcel MVP

WikusOlivier

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Share this page

formula to count unique values based on today's date

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock