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

BryanC72

New Member
Joined
Feb 18, 2018
Messages
5
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
 
Last edited by a moderator:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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))
 
Last edited:
Upvote 0
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.
 
Last edited by a moderator:
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top