Count unique values where multiple columns have data

[Spartin]

See below. I need to count the number of drivers (identified by their unique driver ID in ColumnA) who logged into a system over the given date range (in columns B-E). In the example below, the answer is 2. I'm having trouble counting the unique values given the multiple columns. Any help is appreciated. I am having problems with the formatting on the table below - sorry for about that.


A B C D E
Driver ID 7/1/17 7/2/17 7/3/17 7/4/17

1010003 1 1
1010008
1010011
1010013
1010118 1 1 1
1010121
1010128

 

[NiMip]

Managed to combine some other things I've done before and somehow it worked! Make sure to press CONTROL+SHIFT+ENTER when entering the formula, as it uses arrays. You should see curly brackets appear around the formula if done properly {=...}
=SUM(--(FREQUENCY(IF(B2:E8<>"",A2:A8),IF(B2:E8<>"",A2:A8))>0))

 

[Spartin]

That worked - very nice. Thanks for the assist. That was a new one for me...

 

[Aladin Akyurek]

If driver ID's are number, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(B2:E8),A2:A8),A2:A8),1))

 

[NiMip]

No worries! I just spent far too long agonising over a way to have text (e.g. a name, 1010003A, etc.) instead of just numbers for the ID... text kind of screws things up a bit in the formula I provided.
If you do have IDs that have any combination of numbers and/or letters, you can use the following:
=SUM(--(FREQUENCY(IFERROR(MATCH(IF(B2:E8<>"",A2:A8),A2:A8,0),FALSE),IFERROR(MATCH(IF(B2:E8<>"",A2:A8),A2:A8,0),FALSE))>0))

 

[Aladin Akyurek]

No worries! I just spent far too long agonising over a way to have text (e.g. a name, 1010003A, etc.) instead of just numbers for the ID... text kind of screws things up a bit in the formula I provided.
If you do have IDs that have any combination of numbers and/or letters, you can use the following:
=SUM(--(FREQUENCY(IFERROR(MATCH(IF(B2:E8<>"",A2:A8),A2:A8,0),FALSE),IFERROR(MATCH(IF(B2:E8<>"",A2:A8),A2:A8,0),FALSE))>0))

This board contains zillions frequency formulas for doing a unique count.

If the items to count are not just numbers...

Control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A8),IF(ISNUMBER(B2:E8),MATCH(A2:A8,A2:A8,0))),ROW(A2:A8)-ROW(A2)+1),1))

 

[NiMip]

Cheers, no doubt there are. But I really enjoy solving the puzzle myself, rather than just going straight to the answer. Your formula looks a little simpler than mine but pretty similar concept, so I'm still pretty chuffed!

 

[Spartin]

Ok, so another wrinkle. For my original example, how do I integrate the condition that the driver frequency fall within a date range? For example within 7/1 to 7/3 and not the entire date range.

 

[Spartin]

Awesome - thanks. So another wrinkle. For my original example, how do I integrate the condition that the driver frequency fall within a date range? For example within 7/1 to 7/3 and not the entire date range.

 

[Aladin Akyurek]

Awesome - thanks. So another wrinkle. For my original example, how do I integrate the condition that the driver frequency fall within a date range? For example within 7/1 to 7/3 and not the entire date range.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(B2:E8),IF(C2:C8>=date1,IF(C2:C8<=date2,A2:A8))),A2:A8),1)

still assuming that driver id's are true numbers. By the way, substitute for date1 and date2 the cell addresses housing these date criteria.