Complicated 2D counting

[vichisov]

I have the attached document (simplified, truncated, etc). The real one has 100 names, hundreds of dates etc so this can not be done by hand.

I need to have a count of unique dates that Name X, worked at Location that starts with OP, between the date range in A:A, B:B

I can do a count using a "countifs" or "sumifs" (something like E3) but if the Name moved from one location to the next in the middle of the day, it will overestimate how many days they worked. Is there a way to merge a formulated. Any ideas?

[oeldere]

Post the results for the given period in you file (manualy).

After that I will take a look if I get an solution for you.

[daffodil11]

Will the dates always be out of chronological order?

I can do this if the dates are sequential, but on Sheet1 you go from 1/5 to 1/6 back to 1/5 and that's throwing for a loop. If all the dates were grouped together, even if duplicate, I could filter them out.

[Tony Valko]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(J2:J23>=A3,IF(J2:J23<=B3,IF(N2:N23=A1,IF(LEFT(O2:O23,2)="OP",J2:J23)))),J2:J23),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[vichisov]

It worked perfectly. Thank you!

The one problem with excel though is that Arrays of any size and complexion are seriously taxing w/r to resources. Will this work with a file 40k lines long?

[Tony Valko]

Yes, but it may take a few seconds to calculate.

[vichisov]

Hello,

I have a similar problem and I keep getting a count instead of a unique count. What am I missing? Please see attached.

Thank you!

[vichisov]

NM... Figured it out.