+ Reply to Thread
Results 1 to 7 of 7

Counting of Unique Number as per Criteria on the 2nd status

  1. 07-24-2019, 09:26 PM #1
    jhomai2026
    jhomai2026 is offline
    Registered User
    Join Date
    07-24-2019
    Location
    Singapore
    MS-Off Ver
    MS 2013
    Posts
    3

    Counting of Unique Number as per Criteria on the 2nd status

    Hello Guys

    i have a unique number where it has a revision as per the attached image, unique # is A,B,C,D and each have a revision of 1 & 2.

    Each 1st revision has a status of either AWC or R&R, then it comes to the 2nd revision which has a status as well.

    now my boss wanted me to count each unique # from

    Revision 1(AWC) becomes AWC on the 2nd revision
    Revision 1(AWC) Becomes R&R on the 2nd Revision
    Revision 1(R&R) becomes AWC on the 2nd revision
    Revision 1(R&R) Becomes R&R on the 2nd Revision

    Excel Count.JPG



    Thanks for your kind assistance
    Reply With QuoteRegister To Reply
  2. 07-25-2019, 02:53 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Counting of Unique Number as per Criteria on the 2nd status

    try this:

    =SUMPRODUCT(($B$2:$B$8=1)*($C$2:$C$8=$E10)*($B$3:$B$9=2)*($C$3:$C$9=F$9))

    copied across and down.

    Next time, please post an Excel sheet, not a non-editable picture.
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 07-25-2019, 03:10 AM #3
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Counting of Unique Number as per Criteria on the 2nd status

    You could use Power Query.

    Format your source data as a table, then use:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...
    Reply With QuoteRegister To Reply
  4. 07-25-2019, 03:10 AM #4
    jhomai2026
    jhomai2026 is offline
    Registered User
    Join Date
    07-24-2019
    Location
    Singapore
    MS-Off Ver
    MS 2013
    Posts
    3

    Re: Counting of Unique Number as per Criteria on the 2nd status

    Hi Glenn,

    Wow,, that was great. its Working,.
    all my office-mates are helping to research how to get this formula..

    this will save my day on manual counting on filter,,

    Thank you so much
    Reply With QuoteRegister To Reply
  5. 07-25-2019, 03:14 AM #5
    jhomai2026
    jhomai2026 is offline
    Registered User
    Join Date
    07-24-2019
    Location
    Singapore
    MS-Off Ver
    MS 2013
    Posts
    3

    Re: Counting of Unique Number as per Criteria on the 2nd status

    Hi Olly,

    Thanks for Alternative Solution,, im gonna try as well this one for future reference,, new learning for me the Power Query
    Reply With QuoteRegister To Reply
  6. 07-25-2019, 03:15 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,777

    Re: Counting of Unique Number as per Criteria on the 2nd status

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 07-25-2019, 03:35 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Counting of Unique Number as per Criteria on the 2nd status

    You're welcome.


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Counting unique name values with a criteria
    By Bman84 in forum Excel General
    Replies: 2
    Last Post: 07-06-2017, 12:44 PM
  2. [SOLVED] Counting Number of Unique Values in a List based on Criteria
    By greencroft in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-03-2014, 04:00 AM
  3. Counting unique criteria?
    By theThirdMan in forum Excel General
    Replies: 3
    Last Post: 11-06-2012, 06:41 PM
  4. [SOLVED] Counting unique values with criteria
    By Kevin McCartney in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-21-2006, 08:35 AM
  5. [SOLVED] Counting Unique Values Given Criteria
    By Domenic in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 07:05 PM
  6. [SOLVED] Counting Unique Values Given Criteria
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  7. [SOLVED] Counting Unique Values Given Criteria
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1