+ Reply to Thread
Results 1 to 11 of 11

Unique count and output total count for IDs that meet condition

  1. #1
    Forum Contributor
    Join Date
    07-25-2017
    Location
    UK
    MS-Off Ver
    Office 365 Excel Version 2202 Build 16
    Posts
    184

    Unique count and output total count for IDs that meet condition

    Hi,

    Please see attached workbook with sample data and expected results.

    I require a formula which produces the following:

    Unique count and Output total serial number(s) for corresponding ID WHERE Status = Issued, Renewed or Repair.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Unique count and output total count for IDs that meet condition

    Hi makaveeti,

    Try this formula in F2 and pull it down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If this formula doesn't give the same answer you want, look for spaces in some of your data. That would cause them not to match. IE 5space is not equal to 5nospace

    Note: Column B is blank so you can't sort and have column A stay with columns C and D.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Unique count and output total count for IDs that meet condition

    If you're only wanting to count where the status is ISSUED, RENEWED, or REPAIR, try this:

    =SUM(COUNTIFS($A$2:$A$26,$A2,$C$2:$C$26,{"ISSUED","RENEWED","REPAIR"},$D$2:$D$26,$D2))

    In your expected results, you have 3 rows with the answer of 3 for each, but the S/N's don't match for those - is that an error on your part? There are a couple of others that don't match so maybe I'm not understanding your requirement exactly - I'm assuming the ID AND the S/N's need to match.

  4. #4
    Forum Contributor
    Join Date
    07-25-2017
    Location
    UK
    MS-Off Ver
    Office 365 Excel Version 2202 Build 16
    Posts
    184

    Re: Unique count and output total count for IDs that meet condition

    Hi Greg,

    That's not an error. I'm sorry if my explanation of the logic was poor.

    Rows 9 10 and 11 contain an ID which is 578954125102.

    There are 3 unique serial numbers associated to this ID which have a status of ISSUED:

    8328014380747500
    9192018767647000
    9192018767627000

    Therefore the unique count of serial numbers for that ID = 3

    There is one thing I forgot to mention and that is on my wider dataset, Column A contains some values which are alphanumeric

    Maybe the logic should be to look at a unique ID and do a unique count of serial numbers for that unique ID where the status is ISSUED, RENEWED or REPAIR.

    Hope that makes sense.
    Last edited by makaveeti; 04-05-2022 at 04:54 AM.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Unique count and output total count for IDs that meet condition

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    07-25-2017
    Location
    UK
    MS-Off Ver
    Office 365 Excel Version 2202 Build 16
    Posts
    184

    Re: Unique count and output total count for IDs that meet condition

    Hi Fluff13

    That worked for the sample dataset however on my larger dataset, where I would expect to see a 0, I am seeing an error in the calculation for users who do not have any serial numbers that don't have a status of issued, renewed or repaired. My bad.

    See row 27 on the attached updated sample workbook.

    On a side note I found a formula that I didn't know I had (see Sheet2). It does the same job however it looks up the IDs in sheet1 from sheet2 and then returns the result. Can you adapt that formula so the lookup is removed and place it in Sheet1 Column G?

    Thanks in advance.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Unique count and output total count for IDs that meet condition

    To get rid of the error, you can use
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    07-25-2017
    Location
    UK
    MS-Off Ver
    Office 365 Excel Version 2202 Build 16
    Posts
    184

    Re: Unique count and output total count for IDs that meet condition

    Thanks Fluff13 - That has resolved the issue.

    I'll mark the thread as solved.

    Any chance you could look at the alternative formula in sheet2 of the workbook?

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Unique count and output total count for IDs that meet condition

    The frequency function is not something I really know about, but this seems to give the same result
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    07-25-2017
    Location
    UK
    MS-Off Ver
    Office 365 Excel Version 2202 Build 16
    Posts
    184

    Re: Unique count and output total count for IDs that meet condition

    Yes that appears to give the same results. Thanks for your time with it. I might try and dig out who provided it to me originally to verify that it's being used correctly.

    As for the original question - All solved. Thanks.

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Unique count and output total count for IDs that meet condition

    Glad to help & thanks for the feedback

+ 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. Count Value of Cells that meet a certain condition
    By cdhampshire in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2020, 06:09 AM
  2. Replies: 1
    Last Post: 10-05-2018, 02:36 PM
  3. [SOLVED] Sum Count unique ID if meet the criteria
    By ieyfan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-12-2018, 04:55 AM
  4. Formula Required to Count Rows only if condition is meet or else dont count
    By Shahbazk in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-22-2017, 08:39 AM
  5. [SOLVED] Count if the sum of columns meet a condition
    By my_sunshine in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 03-09-2015, 12:33 PM
  6. Count unique values within a range that meet a specific condition
    By CSS in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-24-2013, 02:00 PM
  7. Count text strings that meet a condition
    By dcgrove in forum Excel General
    Replies: 4
    Last Post: 09-14-2010, 03:38 PM

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