Help with (possible) SUM/COUNTIF formula

[bBop87]

Hello all,

This might be a simple thing but I cannot for the life of me figure this out, so any help would be massively appreciated.

Hope I explain this right, I have 2 columns, column A is the locations that shifts are worked, and column B is the names of the employees working there. What I want is to get a total employee count for each location excluding duplicates. As employees are booked per shift, they appear multiple times in column B, but for counting purposes, I want them to exclude duplicates.

Attached is a basic list, the actual one I am working from would have hundreds of lines for locations/employees. What I am trying to achieve here is in column D&E. I just can't figure out the formula.

Thanks in advance

[alansidman]

A quick and easy solution using Power Query

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Please Login or Register  to view this content.

[AliGW]

Welcome to the forum.

Try this:

=SUM(IF($A$2:$A$27=D2, 1/(COUNTIFS($A$2:$A$27, D2, $B$2:$B$27, $B$2:$B$27)), 0))

[bBop87]

Hi Ali,

Thanks for this, just getting round to trying it out now after a weeks holidays! When I try this formula, for CCS, its dividing by the number of times it appears so is returning 0.333333. The others it returns zero as there are more than 1 employee against it.

[bBop87]

Thanks Alan, Power Query is definitely something I need to look into! Appreciate the links.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.