+ Reply to Thread
Results 1 to 11 of 11

Excel formula for unique count but based on condition

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    35

    Excel formula for unique count but based on condition

    I am looking for a formula to get a unique count of rows, but only the rows meeting certain criteria should be counted. The criteria value is in another column.

    See the sample sheet. I have provided the answer and also how I got the answer using multiple manual steps. Is there a way to automate all that in a single formula?



    Solution:
    This is a Google sheets solution. Unfortunately, there is one COUNTUNIQUE formula that is exclusive to Google sheets and does not work in MS Excel.

    Please Login or Register  to view this content.
    Col C has indicator which is blank or N. We only want to count rows which do not have N
    Col D has actual email address. But it can be blank. We only want rows which have an email address, so not blank.
    Col A has the last name.
    The minus 1 at the end is to eliminate counting 0 which is for rows that do not match the above criteria.
    Attached Files Attached Files
    Last edited by amitdi; 09-15-2022 at 11:40 AM. Reason: Solved

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel formula for unique count but based on condition

    try Power Query

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Excel formula for unique count but based on condition

    or with formula:

    =SUM(IF(Table1[Email]<>"N",1/(COUNTIFS(Table1[Email],"<>N",Table1[Last Name],Table1[Last Name])),0))

  4. #4
    Registered User
    Join Date
    08-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    35

    Re: Excel formula for unique count but based on condition

    Quote Originally Posted by 63falcondude View Post
    or with formula:

    =SUM(IF(Table1[Email]<>"N",1/(COUNTIFS(Table1[Email],"<>N",Table1[Last Name],Table1[Last Name])),0))
    Hi,
    I was able to use this formula and it worked. But I am an idiot since I did not give the full picture. I have 2 columns that have criteria and its an AND criteria.

    So basically, email <> N AND Email Address <> ""

    Email Address is a column where the actual address is populated. So in this sample, the answer is 3 since the last row person does not have email address. I tried editing your formula in vain.

    Also, I will be translating this to Google Sheet formula. I was able to do this easily with above formula using ARRAYFORMULA.
    Attached Images Attached Images
    Last edited by amitdi; 09-14-2022 at 06:29 PM.

  5. #5
    Registered User
    Join Date
    08-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    35

    Re: Excel formula for unique count but based on condition

    Quote Originally Posted by sandy666 View Post
    try Power Query

    Please Login or Register  to view this content.
    yes, that'd work thanks. but i should've posted earlier. my plan is to eventually take this data to google sheets. so i was interested in a formula.

  6. #6
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Excel formula for unique count but based on condition

    Cell B16 formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cell C14 formula , Drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    35

    Re: Excel formula for unique count but based on condition

    Quote Originally Posted by wk9128 View Post
    Cell B16 formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The B16 formula seems to work, thats what I want. But still there is one issue. The formula is hardcoded. The column references $C$2:$C$8 are still fine, because I can always insert rows in between instead of at the end. This way those references auto-adjust the rows.

    But the main problem is the row reference at the end of the formula. Is there a way to make it dynamic? ROW($1:$7)
    I tried inserting a new row in the data and the formula doesnt work, needs adjusting.

  8. #8
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Excel formula for unique count but based on condition

    ANS.POST#7 Please expand the source data range, maybe it can solve your problem

    @amitdi You're Welcome. Glad to help . Thank You for the feedback and rep.

  9. #9
    Registered User
    Join Date
    08-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    35

    Re: Excel formula for unique count but based on condition

    Quote Originally Posted by wk9128 View Post
    ANS.POST#7 Please expand the source data range, maybe it can solve your problem

    @amitdi You're Welcome. Glad to help . Thank You for the feedback and rep.

    Hi,
    This is solved. Thanks.

    But I found an interesting issue, which I also resolved myself. I'll try to explain in case you or anybody else reading this thread later might be interested.

    When I used your formula on my entire data, I was getting the answer 64, but my manual steps were giving the answer 67. Now 67 is the correct answer. On debugging, the issue was in unclear requirement conditions.

    You see, one of the family has following rows

    Family Name/Email/Email Address
    Smith//
    Smith//[email protected]

    Now your formula does not count Smith because the match fails. This is because the email populated is not the 1st row when it encounters Smith. But we need to count Smith, because they have an email address row.

    I started building my own formula in Google sheets and was able to come up with this:

    Please Login or Register  to view this content.
    Since COUNTUNIQUE function does not exist in MS Excel and I dont know of an alternative, I cannot provide an alternative. But thats ok, as I anyways was planning to migrate this to Google sheets.

    Thank you for all the posts as all of the above posts helped me build towards this solution.

  10. #10
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Excel formula for unique count but based on condition

    Hi @amitdi but your profile says OFFICE 2016 if google sheet sorry i'm out

  11. #11
    Registered User
    Join Date
    08-14-2012
    Location
    Canada
    MS-Off Ver
    Excel 2016
    Posts
    35

    Re: Excel formula for unique count but based on condition

    Quote Originally Posted by wk9128 View Post
    Hi @amitdi but your profile says OFFICE 2016 if google sheet sorry i'm out
    Yes, Excel 2016 is what I've installed on my laptop. But its fine, as I found what I was looking for. Thanks for your inputs though, because your input gave me breakthrough.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 22
    Last Post: 04-15-2022, 08:08 PM
  2. [SOLVED] count unique text entries based on another condition
    By edmundo1971 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-26-2015, 09:38 AM
  3. count unique values based on 1 condition
    By penfolda in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-05-2014, 10:28 PM
  4. [SOLVED] Count unique text, excluding space, based on one condition
    By asfa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-01-2013, 02:33 PM
  5. Count unique values based on condition
    By pradeev in forum Excel General
    Replies: 4
    Last Post: 06-12-2012, 10:43 AM
  6. [SOLVED] count duplicate (or, inversely, unique) entries, but based on a condition
    By markx in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2005, 03:06 PM

Tags for this Thread

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