Share via

Combining Frequency with Countif

Anonymous
2016-03-30T15:14:29+00:00

Good Morning

I have imported text from a PDF in col AA.  In col BB I have formulas to strip out the data I need; essentially a list of employee #s and dates (plus some blank cells due to original text formatting).  In col AC and AE I have helpers that repeat the employee

and convert the date to a datecode.  What I need to count is unique dates for each employee number.  All of this is very dynamic; 200 employee #s that may or may not be on each report, widely varying number of entries per employee/date and the blank cells

aren't consistent.

I have tried a few combinations of Countif, Frequency, Sumproduct, etc. with no success.

Bonus points :-D If I could remove the helper columns and reduce size/complexity of this workbook...

Thanks in advance for the help.

Original Text Emp# & Dates Emp# helper Datecode helper Count of unique dates
19525 (18 Uploads, 2162 Records, 4 Leaks) 19525 19525 19525
3/7/2016 11:24:00 AM 19525 195 1 3/7/2016 19525 42436
3/7/2016 12:42:00 PM 19525 185 0 3/7/2016 19525 42436
3/7/2016 2:42:00 PM 19525 181 1 3/7/2016 19525 42436
3/7/2016 3:24:00 PM 19525 197 0 3/7/2016 19525 42436
3/7/2016 3:42:00 PM 19525 69 0 3/7/2016 19525 42436
3/7/2016 4:42:00 PM 19525 76 0 3/7/2016 19525 42436
3/7/2016 5:42:00 PM 19525 110 0 3/7/2016 19525 42436
3/8/2016 11:26:00 AM 19525 211 1 3/8/2016 19525 42437
3/8/2016 11:28:00 AM 19525 14 0 3/8/2016 19525 42437
3/8/2016 2:58:00 PM 19525 237 1 3/8/2016 19525 42437
3/8/2016 3:28:00 PM 19525 60 0 3/8/2016 19525 42437
3/8/2016 4:28:00 PM 19525 124 0 3/8/2016 19525 42437
3/8/2016 5:28:00 PM 19525 57 0 3/8/2016 19525 42437
3/9/2016 2:26:00 PM 19525 197 0 3/9/2016 19525 42438
3/9/2016 2:35:00 PM 19525 37 0 3/9/2016 19525 42438
3/9/2016 4:52:00 PM 19525 3 0 3/9/2016 19525 42438
3/9/2016 5:42:00 PM 19525 198 0 3/9/2016 19525 42438
3/9/2016 5:52:00 PM 19525 11 0 3/9/2016 19525 42438
381 (1 Upload, 9144 Records, 7 Leaks) 381 381 381
3/28/2016 4:29:48 PM Page 142 of 250 381
Attributed Attributed 381
Date Truck ID Records Leaks 381
3/7/2016 3:08:00 PM 381 9144 7 3/7/2016 381 42436
40201 (1 Upload, 29528 Records, 3 Leaks) 40201 40201 40201
3/10/2016 11:23:00 AM 40201 29528 3 3/10/2016 40201 42439
40202 (1 Upload, 42657 Records, 29 Leaks) 40202 40202 40202
3/9/2016 2:47:00 PM 40202 42657 29 3/9/2016 40202 42438
40211 (111 Uploads, 42031 Records, 3 Leaks) 40211 40211 40211
3/7/2016 8:32:00 AM 40211 202 0 3/7/2016 40211 42436
3/7/2016 9:12:00 AM 40211 802 1 3/7/2016 40211 42436
3/7/2016 9:22:00 AM 40211 412 0 3/7/2016 40211 42436
3/7/2016 9:52:00 AM 40211 331 0 3/7/2016 40211 42436
3/7/2016 10:19:00 AM 40211 392 0 3/7/2016 40211 42436
3/7/2016 11:03:00 AM 40211 284 0 3/7/2016 40211 42436
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2016-03-31T13:25:28+00:00

You may use either the above formula or the below formula which has been updated so as to cover blank cells in both columns C & D:

Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell E2 & copy down:

=IF(OR($C2="",$D2=""),"",IF($C2<>$D2,"",SUM(IF(FREQUENCY(IF($C$2:$C$100=$C2,IF($D$2:$D$100<>"",MATCH("~"&$D$2:$D$100,$D$2:$D$100&"",0))),ROW($D$2:$D$100)-ROW($D$2)+1),1))-1))

Regards,

Amit Tandon

Was this answer helpful?

0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-03-31T12:34:04+00:00

    Amit Tandon, I apologize for not explaining correctly.  Reading my original post I see why you calculated the way you did.  But I am looking for the number of unique dates under each employee # rather than the count of each date.  So each employee number would have a result.  For example 19525 would get a 3 since he lists 3/7, 3/8, and 3/9 several times each.

    My response to Vardhaman Shetti may clarify further.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-03-31T12:19:30+00:00

    Vardhaman Shetti, thanks for the reply.  Sorry for the confusion.  Here are my answers to your questions:

    1. I want to count how many individual dates are listed under each employee number.  So, the result for 19525 above would be 3 since he lists 3/7/16, 3/8/16 and 3/9/16 several times each
    2. I added the helper columns to assist with this calculation.  Every cell in the helper columns has it's own formula.  If there is a way to count col B directly, that would be preferable.
    3. Below is the same data with the expected results entered manually.
    Oridinal Text Emp# & Dates Emp# helper Datecode helper Count of unique dates
    19525 (18 Uploads, 2162 Records, 4 Leaks) 19525 19525 19525 3
    3/7/2016 11:24:00 AM 19525 195 1 3/7/2016 19525 42436
    3/7/2016 12:42:00 PM 19525 185 0 3/7/2016 19525 42436
    3/7/2016 2:42:00 PM 19525 181 1 3/7/2016 19525 42436
    3/7/2016 3:24:00 PM 19525 197 0 3/7/2016 19525 42436
    3/7/2016 3:42:00 PM 19525 69 0 3/7/2016 19525 42436
    3/7/2016 4:42:00 PM 19525 76 0 3/7/2016 19525 42436
    3/7/2016 5:42:00 PM 19525 110 0 3/7/2016 19525 42436
    3/8/2016 11:26:00 AM 19525 211 1 3/8/2016 19525 42437
    3/8/2016 11:28:00 AM 19525 14 0 3/8/2016 19525 42437
    3/8/2016 2:58:00 PM 19525 237 1 3/8/2016 19525 42437
    3/8/2016 3:28:00 PM 19525 60 0 3/8/2016 19525 42437
    3/8/2016 4:28:00 PM 19525 124 0 3/8/2016 19525 42437
    3/8/2016 5:28:00 PM 19525 57 0 3/8/2016 19525 42437
    3/9/2016 2:26:00 PM 19525 197 0 3/9/2016 19525 42438
    3/9/2016 2:35:00 PM 19525 37 0 3/9/2016 19525 42438
    3/9/2016 4:52:00 PM 19525 3 0 3/9/2016 19525 42438
    3/9/2016 5:42:00 PM 19525 198 0 3/9/2016 19525 42438
    3/9/2016 5:52:00 PM 19525 11 0 3/9/2016 19525 42438
    381 (1 Upload, 9144 Records, 7 Leaks) 381 381 381 1
    3/28/2016 4:29:48 PM Page 142 of 250 381
    Attributed Attributed 381
    Date Truck ID Records Leaks 381
    3/7/2016 3:08:00 PM 381 9144 7 3/7/2016 381 42436
    40201 (1 Upload, 29528 Records, 3 Leaks) 40201 40201 40201 1
    3/10/2016 11:23:00 AM 40201 29528 3 3/10/2016 40201 42439
    40202 (1 Upload, 42657 Records, 29 Leaks) 40202 40202 40202 1
    3/9/2016 2:47:00 PM 40202 42657 29 3/9/2016 40202 42438
    40211 (111 Uploads, 42031 Records, 3 Leaks) 40211 40211 40211 1
    3/7/2016 8:32:00 AM 40211 202 0 3/7/2016 40211 42436
    3/7/2016 9:12:00 AM 40211 802 1 3/7/2016 40211 42436
    3/7/2016 9:22:00 AM 40211 412 0 3/7/2016 40211 42436
    3/7/2016 9:52:00 AM 40211 331 0 3/7/2016 40211 42436
    3/7/2016 10:19:00 AM 40211 392 0 3/7/2016 40211 42436
    3/7/2016 11:03:00 AM 40211 284 0 3/7/2016 40211 42436

    Thanks again.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-03-31T09:20:27+00:00

    Using your columns for this - refer below image:

    Enter below formula in cell E2 & copy down:

    =IF(OR($C2="",$D2=""),"",IF($C2=$D2,"",IF(SUMPRODUCT(--($C1:$C$2=$C2),--($D1:$D$2=$D2))>0,"",SUMPRODUCT(--($C$2:$C$100=$C2),--($D$2:$D$100=$D2)))))

    Regards,

    Amit Tandon

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-03-31T07:21:50+00:00

    Hello Steve,

    Thank you for posting your query in Microsoft Office Community.

    Before we proceed, I need more information to help you better:

    1. When you say 'unique dates for each employee number' do you want to know what are different dates for each employee?
    2. What do you mean by 'If I could remove the helper columns and reduce size/complexity of this workbook'? Please elaborate.
    3. Could you pleas explain with example what exactly result are you expecting?

    Kindly reply with necessary information and I’ll be happy to help you further.

    Thank you.

    Was this answer helpful?

    0 comments