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 a column C I have date

column B e.g.
[TABLE="width: 183"]
<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><COLGROUP><COL></COLGROUP>[/TABLE]



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

Appreciate the help

 

[Eric W]

Perhaps:

=SUMPRODUCT(--($C$1:$C$6=TODAY()),--(MATCH($B$1:$B$6,$B$1:$B$6,0)=ROW($B$1:$B$6)))

Change the ranges to match your sheet. If your top row is not 1, use this instead:

=SUMPRODUCT(--($C$2:$C$6=TODAY()),--(MATCH($B$2:$B$6,$B$2:$B$6,0)=ROW($B$2:$B$6)-ROW($B$2)+1))

 

[p4nny]

Thanks for the reply.

Formula works..

Only issue is, if I extend the range to include blank cells, I get an error.

 

[Eric W]

You can add the IFERROR function around it:

=IFERROR(SUMPRODUCT(--($C$2:$C$6=TODAY()),--(MATCH($B$2:$B$6,$B$2:$B$6,0)=ROW($B$2:$B$6)-ROW($B$2)+1)),"")

 

[p4nny]

Hi,

My question is, why would this error?

This is the exact formula I've used:

=SUMPRODUCT(--($K$13:$K$4000=TODAY()),--(MATCH($B$13:$B$4000,$B$13:$B$4000,0)=ROW($B$13:$B$4000)-ROW($B$13)+1))

The range K13:K4000 (DATE) & B13:B4000 (ID to be unqiue'd) do contain blank cells.. As a result the formula does not work.

Appreciate the help and guidance

 

[Eric W]

Perhaps I've misunderstood your requirements. That formula should provide the number of unique items with today's date. Do you instead want a count for each item of how many items there are with today's date?

 

[Aladin Akyurek]

Hi

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

column B e.g.
[TABLE="width: 183"]
<tbody>[TR]
[TD]151_92861310053100[/TD]
[/TR]
[TR]
[TD]039_6B7453EFADA0B0FFF1986A56E61F20FC[/TD]
[/TR]
[TR]
[TD]151_92861310053100[/TD]
[/TR]
[TR]
[TD]151_92861310282700[/TD]
[/TR]
[TR]
[TD]151_92861310282700[/TD]
[/TR]
[TR]
[TD]151_92861310282700[/TD]
[/TR]
</tbody>[/TABLE]



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

Appreciate the help

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$400=""),IF($C$2:$C$400=TODAY(),MATCH("~"&$B$2:$B$400,$B$2:$B$400&"",0))),ROW($B$2:$B$400)-ROW($B$2)+1),1))