Count items not included in a data validation range sourced from named range

Hi All,

How to phrase this!? I'm trying to find a formula that will count the number of items not included in a data validation range from another named range when the validation list is sorced from a seperate named range that has other entries not in the first named range.

The specific workbook is attached.

In this workbook, sheet 'Staff Data' has three named ranges - StaffList, Management and ValidationList. ValidationList is the combination of StaffList & Management and is literally the validation source for the staff columns on the sheet named 'Schedule'.

The requirement:
Cell D3 = Number of staff in range ("D5:D52") that have not been selected from StaffList.
Cell B2 = The total number of staff in StaffList (already there).

I need to exclude names selected from the Management named range that have been used in range ("D5:D52") as they are not considered regular staff but at times are added to the Schedule. There is never an issue of the same name being on either StaffList or Management as surnames are normally included but have been removed here to keep my staff anonymous.

I will need to be able to apply the formula to columns F, H, J, L, N & P and to additional sheets in the workbook.

Many thanks in advance for any assistance.

Regards,

I think this is what you want:
=$B$2-SUMPRODUCT(COUNTIF(D5:D52,StaffList))

Enter as an array formula (hit Ctrl-Shift-Enter instead of just Enter when inputting the formula).

Hi Pauleyb,

Thanks for the response but your formula does not take into consideration duplicates. It counts names that appear in the validation range more than once where my goal is to have only the number of names not yet used. I have attached an example of what I'm trying to achieve.

In the attached work book, on the 'Schedule' sheet, validation for range ("D5:D52") is sourced from the named range called ValidationList in the 'Staff Data' sheet. On the 'Schedule' sheet, Cell D3 = Number of staff in range ("D5:D52") that have not been selected from the named range StaffList found in the 'Staff Data' sheet. I have set up two examples in the MONDAY and TUESDAY columns on the Schedule. The names not used from StaffList are recorded from row 54 down in each staff column, The count of these remaining names is what should be in D3 and F3 respectively.

Hope this helps!

Many thanks,

=$B$2-SUMPRODUCT(--(COUNTIF(D5:D52,StaffList)>0))

This does not need to be entered as an array formula.

Hi Pauleyb,

That works perfectly. So simple in the end! Thanks for your assistance.

Regards,