Counting Unique Values in One Column Based on Criteria in Other Columns

[Danni317]

 

Hi All,

I have a massive spreadsheet which I run on a weekly and monthly basis.  The data represents time worked by employees on each job.  Therefore, each employees can have many lines for a single day.

The start and end dates for the report required are in cells D1 & E1 respectively.  Based on these dates, I have column AV displaying whether the line is TRUE (ie., within the date range) or FALSE (ie., not in the date range).

I am able to find the hours worked within the date range in each area for employees and contractors using sumifs (see rows 3 to 10).  However, I also need a headcount (based on Column AM).  The problem is that the same employee/contractor can show up many times and I only need to count them once showing which work area and whether they are a contractor or employee.

Can anyone help? I have attached a sample sheet for your ref.  

[#2]

@Danni317 

You attached an image rather than an actual sample Excel spreadsheet. If you are able to post an actual sheet, make sure it doesn't include the names or other identifiable info on any real people--use fake names.

In the absence of an actual sheet, here are some ideas:

• The UNIQUE function might work (perhaps in conjunction with COUNT or COUNTA) to give you that headcount. UNIQUE does require, however, the most recent version of Excel. Here's a video that does a good job of introducing UNIQUE as well as some other "Dynamic Array" functions. https://www.youtube.com/watch?v=9I9DtFOVPIg
• It's also possible that the Pivot Table feature could be used to produce your desired summaries. https://exceljet.net/glossary/pivot-table

[#3]

Hi,

Thanks for getting back to me.  How do I attach a spreadsheet?  I've already got rid of all the identifying information.

Cheers,

Danni

[#4]

@Danni317 

If you open the full text editor, then down below it you should see this area where you can drag and drop your file(s)

If that doesn't work, click on my hyperlinked name and send me a private message and attach your file there.