+ Reply to Thread
Results 1 to 17 of 17

Count unique values in three columns depending on multiple values

  1. #1
    Registered User
    Join Date
    12-19-2011
    Location
    Montreal,Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    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
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-19-2011
    Location
    Montreal,Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique values in three columns depending on multiple values

    Hi

    Would you like to attach a sample workbook??
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    12-19-2011
    Location
    Montreal,Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    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. #7
    Registered User
    Join Date
    12-19-2011
    Location
    Montreal,Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    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. #9
    Registered User
    Join Date
    12-19-2011
    Location
    Montreal,Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #10
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    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. #11
    Registered User
    Join Date
    12-19-2011
    Location
    Montreal,Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    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.
    Attached Files Attached Files

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    Re: Count unique values in three columns depending on multiple values

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

  13. #13
    Registered User
    Join Date
    12-19-2011
    Location
    Montreal,Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    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.
    Attached Files Attached Files

  14. #14
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    Re: Count unique values in three columns depending on multiple values

    Hi,

    Does the attached help.

    A VBA solution would also be another option.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    12-19-2011
    Location
    Montreal,Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #16
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    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
    Last edited by Richard Buttrey; 01-11-2012 at 10:45 AM.

  17. #17
    Registered User
    Join Date
    12-19-2011
    Location
    Montreal,Canada
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Count unique values in three columns depending on multiple values

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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