Count within multiple criteria when one of the criteria is a date field

[BryanC72]

Hi Guys,

Please help.

I am trying to count the number of days the adjustment user worked from a data set with multiple records for any given day.

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD]ADJUSTMENT USER

[/TD]
[TD]POSTING DATE

[/TD]
[/TR]
[TR]
[TD]KXE
KXE
KXE
KXE
KXE
KXE
KXE
KXE
KXE
KXE
KXE

[/TD]
[TD]1/01/2018
1/01/2018
3/01/2018
3/01/2018
3/01/2018
4/01/2018
4/01/2018
5/01/2018
6/01/2018
6/01/2018
6/01/2018

[/TD]
[/TR]
</tbody>[/TABLE]
















So what I need to do is search a larger data set with multiple Adjustment users and then count how many actual days worked each of them did, NOT the amount of transactions per day they did.

So with the above data set I would be trying to get a result of 5 days worked for Adjustment user KXE.

Please if anyone can give me a formula to count and consolidate the days work for the Adjustment user that would be really appreciated.

Thanks

Bryan

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$12="KXE",$B$2:$B$12),$B$2:$B$12),1))

With a bit more control...

=SUM(IF(FREQUENCY(IF(ISNUMBER($B$2:$B$12),IF($A$2:$A$12="KXE",$B$2:$B$12)),$B$2:$B$12),1))

 

[BryanC72]

Thanks Aladin,

Works great for the most part. The only thing it is doing is, it's putting a 1 in the cell if the Adjustment User hasn't worked at all. How can I change the formula to leave this situation blank. I have tried replacing the 1 with "" or even a 0 at the end of the formula but get #value error.

 

[Aladin Akyurek]

Thanks Aladin,

Works great for the most part. The only thing it is doing is, it's putting a 1 in the cell if the Adjustment User hasn't worked at all. How can I change the formula to leave this situation blank. I have tried replacing the 1 with "" or even a 0 at the end of the formula but get #value error.

Apply the second formula: Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(ISNUMBER($B$2:$B$12),IF($A$2:$A$12=D2,$B$2:$B$12)),$B$2:$B$12),1))

where D2 houses an Adjustment User of interest._{^{<strike>
</strike>}}​

 

[BryanC72]

Thanks again.

 

[Aladin Akyurek]

Thanks again.

You are welcome.