Count Unique Names across multiple columns for specific date

Hi Kattie,

I can do this problem but it takes some tools. The first thing to do is to "UnPivot" your data so the staff are all in the same column. I did this using a "Power Query" UnPivot function. If you don't have the Power Query Add-In you can install it from:
https://www.microsoft.com/en-us/down...c-c1f269050d5c

Then I created two columns next to the unpivoted data. The first appended the Date and Staff. The second did a countif from the top to show duplicates of the same date and staff.

Then I created a pivot table where I filtered your data only showing a 1 on the count from the top. This is then the number you wanted.

I'm sorry it is a little involved but learning how to Unpivot and then the Helper Column (from the top) are great tools to add to your Excel toolbox.
See my answer attached.
Unpivot then Count Unique DateStaff.xlsx

Hello
I was working on this when Marvin replied. If you can't use Marvin's Pivot option, then perhaps you could adapt this formula method. The formula uses as dynamic named range (rng) and assumes that the dates are grouped together as in your example. Conditional formatting highlights the selected range to check the count. If I've understood you correctly, it seems to give the correct count.

DBY

Yes. Sorry. I've amended the attachment. This would be the new formula for the dynamic range:

Formula:

Please Login or Register  to view this content.

There can be nothing in the first header row but the headers you add unless you restrict the COUNTA function.

'Date' is another dynamic range to add dates in column A. Same principle as the header row applies here.

This is rather cumbersome. I couldn't find a way to do the same dynamic range with OFFSET. It would be more compact.

Array entered in L2 and filled down in your last upload. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

=SUM((INDEX($C$2:$H$144,MATCH(1,1/(K2=$A$2:$A$144),0),):INDEX($C$2:$H$144,MATCH(2,1/(K2=$A$2:$A$144),1),)<>"")/COUNTIF(INDEX($C$2:$H$144,MATCH(1,1/(K2=$A$2:$A$144),0),):INDEX($C$2:$H$144,MATCH(2,1/(K2=$A$2:$A$144),1),),INDEX($C$2:$H$144,MATCH(1,1/(K2=$A$2:$A$144),0),):INDEX($C$2:$H$144,MATCH(2,1/(K2=$A$2:$A$144),1),)&""))

Here is a simpler formula than my previous post. Also in L2 and filled down. It does not need to be array entered.

Formula:

Please Login or Register  to view this content.

=SUMPRODUCT((OFFSET(INDEX($C$2:$C$144,MATCH(K2,$A$2:$A$144,0)),0,0,COUNTIF($A$2:$A$144,K2),6)<>"")/COUNTIF(OFFSET(INDEX($C$2:$C$144,MATCH(K2,$A$2:$A$144,0)),0,0,COUNTIF($A$2:$A$144,K2),6),OFFSET(INDEX($C$2:$C$144,MATCH(K2,$A$2:$A$144,0)),0,0,COUNTIF($A$2:$A$144,K2),6)&""))