Matching Criteria against a range when criteria is not in contiguous cells??

GMorris · #1 08-14-2014, 05:06 PM

Can someone help me please?

I want to match criteria (individual employee names) with a range (list of all employees) to count how many 'individuals' were on shift on any given day. The criteria is not in contiguous cells and I think this is my problem?? The names of employees on shift (the criteria) are in Colums E2, G2, I2 and K2.

Lets say the range is a list of 10 employees on the second Tab called "Employees", I have tried something like this: -

=COUNTIF (Employees!A1:A10, "E2,G2,I2,K2") but it does not work - obviously because I know little in Excel

NB. An employee can do a 'double shift' e.g. their name can appear twice in one day if they work AM and PM, but I only want to match and count their name once as I just want to know how many people were on duty any given day.

There are a maximum of 31 rows as the data is recorded daily for each month but I can drag the formula (once I have it) to populate all rows.

Thank you

macropod · #2 08-14-2014, 08:04 PM

Without knowing more about your workbook structure, it's hard to provide a solution. That said, you might be interested in posts 1 & 14 this thread: https://www.msofficeforums.com/excel...once-even.html

Otherwise, perhaps you could attach a workbook to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

GMorris · #3 08-14-2014, 09:24 PM

I just want to count how many individuals were on duty on any given day. I thought I might be able to do this by comparing the employee names (Criteria captured in columns "F" "G" "I" & "K") with the list of all employee names on the second tab??

macropod · #4 08-14-2014, 09:52 PM

As per the link in my previous post, you can calculate the # employees on a given day with:
=SUMPRODUCT((D2:K2<>0)/COUNTIF(D2:K2,D2:K2))-(COUNTIF(D2:K2,"Nobody on Duty")<>0)*1
in L2 and copied down as far as needed.

GMorris · #5 08-14-2014, 10:54 PM

Thanks so much but I just tried the formula and it gives me an incorrect total e.g. row 2 says 4 when it should be 3 (Employee3 + Employee5 + Employee8) and row 3 says 5 when it should be 3 (Employee3 + Employee5 + Employee8 + nobody on shift) etc. Any ideas?

macropod · #6 08-15-2014, 12:58 AM

Although a rather more complex could be made to do the job, this might be better handled via a UDF:

Code:

Function UniqueCount(Src1 As Range, Optional Src2 As Range, Optional Src3 As Range, _
  Optional Src4 As Range, Optional Src5 As Range, Optional Src6 As Range) As Long
Application.Volatile
Dim i As Long, StrSrc As String, StrTmp As String
StrSrc = Src1
If Not Src2 Is Nothing Then StrSrc = StrSrc & "," & Src2
If Not Src3 Is Nothing Then StrSrc = StrSrc & "," & Src3
If Not Src4 Is Nothing Then StrSrc = StrSrc & "," & Src4
If Not Src5 Is Nothing Then StrSrc = StrSrc & "," & Src5
If Not Src6 Is Nothing Then StrSrc = StrSrc & "," & Src6
StrTmp = "|"
  For i = 0 To UBound(Split(StrSrc, ","))
    If InStr(StrTmp, "|" & Split(StrSrc, ",")(i) & "|") = 0 Then
      If Split(StrSrc, ",")(i) <> "" And Split(StrSrc, ",")(i) <> "Nobody on Duty" Then
      StrTmp = StrTmp & Split(StrSrc, ",")(i) & "|"
      End If
    End If
  Next
UniqueCount = UBound(Split(StrTmp, "|")) - 1
End Function

To use it, simply add the code to a normal code module in your workbook and use a formula like:
=UniqueCount(E2,G2,I2,K2)
As code, the function can handle from 1 to 6 input ranges, giving you a bit more flexibility in case you need it.

LuckyPunch · #7 08-15-2014, 01:23 AM

Hi GMorris,

Try this
=SUM(IF(FREQUENCY(MATCH(D2:K2,D2:K2,0),MATCH(D2:K2 ,D2:K2,0))>1,1))

Enjoy

macropod · #8 08-15-2014, 01:43 AM

That won't work for the same reason my previous formula doesn't...

LuckyPunch · #9 08-20-2014, 12:59 AM

Quote:

Originally Posted by macropod

That won't work for the same reason my previous formula doesn't...

It works for me...

macropod · #10 08-20-2014, 02:15 AM

I did and it doesn't. That you should persist with this shows you haven't understood the question...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Lookup using 2 criteria	Itsbarbara	Excel	16	10-07-2013 02:53 PM
Counting cells with multiple complex criteria	TishyMouse	Excel	12	12-06-2012 05:05 AM
Change values in cells based on criteria	SaneMan	Excel Programming	2	02-02-2012 07:58 AM
Selecting blank cells in criteria	apolloman	Excel	6	08-24-2011 05:38 AM
How to count cells containing data and meet certain criteria	AdamNT	Excel	1	08-11-2006 11:51 PM