Counting cells in one column based on unique values in another column

**3SixOne** · 08-11-2017, 10:01 AM

Hi all,

I need a bit of a hand please. Hope I explain this correctly to you -

Attached is a condensed version of a spreadsheet I use to schedule work. What I'd like to be able to do is calculate the amount of engineers I require on any given day.

I need to be able to count the number of cells that have a value in it (Columns H (Engineer 1), I (Engineer 2), J (Engineer 3)& K (Engineer 4)). I don't need to know who is going, so what value is in the cell doesn't matter, only just that the cell has a value in it.

The catch is that I only need to count them based on the unique values in column A. As a job could potentially have 5 or 6 rows associated to it in the spreadsheet, I don't want excel to think that I need six engineers when I only need one.

I then need to be able to display the information per day.

Thanks for your help!

**etaf** · 08-11-2017, 04:21 PM

take 47281
that would be 2
what if you had different names in row h2,h3,h4
does tbc count

**3SixOne** · 08-15-2017, 05:55 AM

Sorry for replying so late. Thanks for the reply.

Correct, 47281 should be 2. And yes, TBC does count (I just haven't yet decided who will be assigned to that particular job).

h2,h3 & h4 should never have different names in it. If that is the case it's a mistake on my part. (I don't need to over complicate things and have the formula flag this mistake to me)

So, taking Monday as an example -

47261 should count 2
48033 - 2
74832 - 2
69400 - 2
04872 - 2
11830 - 3
73194 - 3
12284 - 2

Taking h2,h3 & h4 as an example again; the main thing is I don't want the formula to count separate values in those cells (sometimes there may be a spelling mistake in the engineers name or something), it only needs to recognise an empty cell as 0, or a combined value a 1 for those 3 cells if anything is in them.

Hope I'm explaining this correctly!

Thanks again for your help,

Edit:
Thinking about it, we often filter the spreadsheet by the values in column C (Project). If the formula alters based on this that'll be incredibly useful.

**etaf** · 09-03-2017, 10:18 AM

if you filter , i dont think a formula will allow for filtered data
we may have to use a helper column

**3SixOne** · 09-13-2017, 06:23 AM

Interesting, I didn't know that.

To be honest that part of it is secondary, if the formula can't change when information is filtered then so be it.

Counting cells in one column based on unique values in another column

LinkBack

Thread Tools

Rate This Thread

Display

Counting cells in one column based on unique values in another column

Re: Counting cells in one column based on unique values in another column

Re: Counting cells in one column based on unique values in another column

Re: Counting cells in one column based on unique values in another column

Re: Counting cells in one column based on unique values in another column

Thread Information

Users Browsing this Thread

Similar Threads

Counting unique values based on another column

Count unique values in one column based on conditions and unique values in another column

Counting unique values based on condition in a different column

[SOLVED] Filter rows based on unique values in one column + specific value in another column

Counting Unique text in column B based on a criteria from column a

[SOLVED] Counting # of specific values in one column for each unique IP in another column

Counting unique values in Column B, per value in Column A

Bookmarks

Bookmarks

Posting Permissions