# Count unique values in three columns depending on multiple values

1. ## Count unique values in three columns depending on multiple values

Hi,
I am searching for a formula that will enable me to count unique values on three rows(for example I have names in three columns that may repeat themselves (tech1,tech2,tech3)), but based on two other values. (ex: week# in one column and in another I have day or night work.) Can anybody help me out?

Thanks

2. ## Re: Count unique values in three columns depending on multiple values

Hi,

The usual technique is to use a helper column which concatenates the values in all three columns and then use a COUNTIF() function in another column which looks at the helper column.

Regards

3. ## Re: Count unique values in three columns depending on multiple values

Hello,
Unfortunetly I do not see how I will be able to get the unique values of my three columns if I concatenate them and the countif does not do that either. There is one formula that was on your forum and was close to what I needed : Count Unique Values with Multiple Criteria and the formula was the following :
=SUM(IF(FREQUENCY(IF(A\$2:A\$21=K4,MATCH(B\$2:B\$21,B\$2:B\$21,0)),ROW(B\$2:B\$21)-ROW(B\$2)+1),1)) with array. the only problem with that is that the data is in one column.

4. ## Re: Count unique values in three columns depending on multiple values

Hi

Would you like to attach a sample workbook??

5. ## Re: Count unique values in three columns depending on multiple values

Here it is! I kept only the columns needed. So basically I need the unique count value of columns E,F and G depending on the value of column B and I.

sample.xlsx

6. ## Re: Count unique values in three columns depending on multiple values

Hi,

Now that you've attached the workbook can you tell us what results you expect and importantly why and how you are calculating them. Add your calculated results manually in say column K and explain your workings.

Regards

7. ## Re: Count unique values in three columns depending on multiple values

Hello,
Sorry I should have been more specific. The results I am looking for are in column H (done manually except for H3). Ideally this information I would be able to get through a Pivot table or if needs be I will enter the formula manually so it shows up in my pivots. I need the unique count of tech names(column e,f and g) for each week(column b) and for day or night work(column i). So I would need two results per week for days and nights. I hope I am not confusing anyone...
sample.xlsx

8. ## Re: Count unique values in three columns depending on multiple values

Hi,

I'm sorry but this is still confusing. I only mentioned column K as an example. I realise you want your results in column H but there are only three formulae, two of which are Divide by zero errors. With the H3 formula evaluating to 4 I don't understand (apart from the formula itself) how it is providing you with the result you expect which is related in some way to unique counts.

Please add all your manually calculated results and explain how you arrive at them, and then we should be able to work out a formula (or Pivot table) to achieve this.

9. ## Re: Count unique values in three columns depending on multiple values

Hi, sorry my attachement did not update with the changes I had made to them.. here it is.sample.xlsx

10. ## Re: Count unique values in three columns depending on multiple values

Hi,

I don't see any difference between this file and the last one you uploaded!

11. ## Re: Count unique values in three columns depending on multiple values

Hello again! Sorry for the long delay (vacation time + holidays :o) ) ok so I hope it works this time here is my sample workbook. Basically I am trying to get a formula (and hopefully input it in a pivot table) that will count the unique names (column E,F and G) depending on the week# (column B) and depending on if it was night work or day work (column I). Results needed are manually entered in column H.

12. ## Re: Count unique values in three columns depending on multiple values

Please explain how you work out the values in column H.

13. ## Re: Count unique values in three columns depending on multiple values

I manually counted the unique number of times a name (within the 3 columns (e,f & g)appears within the same week(b) and within the same time of day (column i).
If I define a name for the week (ex: call it week 42(\$E\$5:\$G\$12):J3 ) I can get the count of unique names for that week but then again it dosen't work if there is blanks in there (it works with no blanks called weeks42(\$E\$5:\$F\$12): appearing in J4 ).
I can't work out the formula to add another condition in order to add if it's day or night. I added the attachement with the formulas in column J (sum(1/countif(week42,week42)) formula in array.

14. ## Re: Count unique values in three columns depending on multiple values

Hi,

Does the attached help.

A VBA solution would also be another option.

15. ## Re: Count unique values in three columns depending on multiple values

Hi! That is great! it is perfect! I'm just not getting why the formula is not working on two lines (L3 (should get 2 and L9 should get 1)?

16. ## Re: Count unique values in three columns depending on multiple values

Sorry, my mistake. After I completed the formulae I added the text 'Helper Columns' in L2 to, well help you!
Unfortunately L3 was then compromised by this. Just delete the L2 text. In fact the If(AND(L2.. bit in L3 is only needed because it's the first row and the cell above will always be empty - or should be.

L9 should not have a 1 since the N9 '42DaySJ' already appears above it in N7. i.e. SJ has already been counted once for this particular week number and day/night value.

Regards

17. ## Re: Count unique values in three columns depending on multiple values

Ah yes! perfect! thank you so very much!!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1