+ Reply to Thread
Results 1 to 12 of 12

Challenging complex formula- unique count for multiple criteria on several levels

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    normal, il
    MS-Off Ver
    Excel 2010
    Posts
    66

    Angry Challenging complex formula- unique count for multiple criteria on several levels

    Hello!

    I need some help with a complex formula. I have thousands of rows of data that is continuously increasing.

    There are two ways the data can be looked at (across Type only and across Type and Size combo).

    I need to find the number of times for each a month/year and CP type in which there was a problem. Specifically, if a Type fails (once or more) on any of the series (1-6), then it needs to be counted ONLY ONCE.

    In addition, for both these ways, the data needs to be parsed by month/year and CP type.

    For Type Only
    If everything is ok for that Type, then it is a "0" for that Type
    If one or more problems are identified within a Type, then it needs to be counted only ONCE (no matter how many problems were identified)

    For Type and Size combination
    If everything is ok for that type and size combo, then it is counted as a "0"
    If one or more problems are identified within a Type and Size combo, then it needs to be counted only ONCE (no matter how many problems were identified)

    I am fine with adding columns to help with the formulas.

    I included mock data along with what that data should look like. Its pretty complicated, so I colored the data to help identify the Type (color) and Type and Size (different color shading).

    Note:
    I updated the spreadsheet based on feedback for clarity.
    - Type, Size, OK? (only descriptions), and Date can be anything. These values cannot be unsed within the formula.
    - CP, OK? (only 'Yes'), and Series are "fixed". These values can be used within the formula.



    This is really kicking my behind and I really need some experts to help figure it out.


    Thanks in advance for helping me with this tough formula.
    Joanne
    Attached Files Attached Files
    Last edited by joannelittell; 10-29-2013 at 10:31 AM. Reason: clarified spreadsheet based on feedback

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,265

    Re: Difficult complex formula challenge- singular count for multiple criteria levels

    Pls check the file, hope it works


    Azumi
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Difficult complex formula challenge- singular count for multiple criteria levels

    Hello joannelittell , As per of your requirement if there is any problem it should be counted as once but here in your example file the counting is more than once in the month of March 2013.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Registered User
    Join Date
    04-23-2013
    Location
    normal, il
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Difficult complex formula challenge- singular count for multiple criteria levels

    @ Azumi
    This is amazing, unfortunately i just used simple fake data for demonstration purposes only. Type, Size, Color, etc. can be anything (for instance, Type is a descriptive document name that can be anything). I have updated my data to be more clear as to what is variable and what is not.

    Like i said, this is very complex.

    Sorry for not being crystal clear I really appreciate your effort!



    @ hemesh
    I did have the wrong value in there. sorry for the confusion and good catch!! I updated the spreadsheet for clarity.

    Thanks
    Joanne
    Last edited by joannelittell; 10-29-2013 at 10:32 AM.

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Challenging complex formula- unique count for multiple criteria on several levels

    Hello Find the attached

    First You need to have unique values for Type Column and Unique values for Type and Size in another column then You can easily compile them. In unique values for type and size column you can combine two value by ampersand (symbol) like in A1 you have john & in B1 you have three in C1 type = A1&B1 and you will get johnthree
    Attached Files Attached Files
    Last edited by hemesh; 10-29-2013 at 02:09 PM.

  6. #6
    Registered User
    Join Date
    04-23-2013
    Location
    normal, il
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Challenging complex formula- unique count for multiple criteria on several levels

    Quote Originally Posted by hemesh View Post
    Hello Find the attached

    First You need to have unique values for Type Column and Unique values for Type and Size in another column then You can easily compile them. In unique values for type and size column you can combine two value by ampersand (symbol) like in A1 you have john & in B1 you have three in C1 type = A1&B1 and you will get johnthree
    The comparisions that you have created, you use the new column values specifically within the formula. what happens when more data is added? It looks like I would have to update the formula when data is added.

  7. #7
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Challenging complex formula- unique count for multiple criteria on several levels

    Yes you need to add specific criteria otherwise you will not get the results. But with this formula you need to update in the top cell then you can drag down and across you don't need to create separate formula formula for each cell.

    Other way to do is to create a separate sheet with all unique values which will update dynamically and then sum the values. which will update when data is added to main sheet.

  8. #8
    Registered User
    Join Date
    04-23-2013
    Location
    normal, il
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Challenging complex formula- unique count for multiple criteria on several levels

    OK, I am currently working on something similiar to your second paragraph.

    I will keep at it. thanks!!

  9. #9
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Challenging complex formula- unique count for multiple criteria on several levels

    In your sheet, try this formulas play which seems to give the desired results indicated
    Note: Along the way I did assume that Type or Size will not duplicate across CP ...

    Put in U3: =IF(C3="","",IF(COUNTIF(C$3:C3,C3)>1,"",ROWS($1:1)))
    Copy to V3, fill down to V60, ie cover the extent of data for Type and Size. The above 2 cols will be referred to/used later to extract the uniques listing for Type and Size

    Types
    ---------
    Put in W2:
    =IF(COLUMNS($A:A)>COUNT($U$3:$U$60),"",INDEX($C$3:$C$60,SMALL($U$3:$U$60,COLUMNS($A:A))))
    Copy across to say, AC2. This lists all the unique Types, neatly packed to the left

    Put in W1: =IF(W2="","",INDEX($B:$B,MATCH(W2,$C:$C,0)))
    Copy across to AC1 (The note on the assumption made applies here)

    Now to populate the checks by the mth/yr, reference is made to the time periods header data that is in I4:I15

    Put in W3:
    =IF(SUMPRODUCT(($C$3:$C$60=W$2)*(TEXT($G$3:$G$60,"mmm yyyy")=TEXT($I4,"mmm yyyy"))*(TRIM($F$3:$F$60)<>"Yes"))>0,W$1,"")
    Copy across / fill down to AC14

    Size
    ----------
    Put in AE2:
    =IF(COLUMNS($A:A)>COUNT($V$3:$V$60),"",INDEX($D$3:$D$60,SMALL($V$3:$V$60,COLUMNS($A:A))))
    Copy across to say, AO2. This lists all the unique Sizes, neatly packed to the left

    Put in AE1: =IF(AE2="","",INDEX($B:$B,MATCH(AE2,$D:$D,0)))
    Copy across to AO1 (The note on the assumption made applies here)

    Similarly, as done for Types, to populate the checks by the mth/yr, reference is made to the time periods header data that is in I4:I15

    Put in AE3:
    =IF(SUMPRODUCT(($D$3:$D$60=AE$2)*(TEXT($G$3:$G$60,"mmm yyyy")=TEXT($I4,"mmm yyyy"))*(TRIM($F$3:$F$60)<>"Yes"))>0,AE$1,"")
    Copy across / fill down to AO14

    With the above constructs done,
    Put in J4: =COUNTIF($W3:$AC3,J$3)
    Copy across / fill down to L15 to get the results for "Across Type only"

    Similarly for Size ....
    Put in O4: =COUNTIF($AE3:$AO3,O$3)
    Copy across / fill down to Q15 to get the other set of results
    -------------------------------------------------------
    Success? Wave it, hit the little star at the bottom left of my responses
    Last edited by Max, Singapore; 10-30-2013 at 09:48 PM. Reason: Corrections for mis-alignment: Steps and formulae revised

  10. #10
    Registered User
    Join Date
    04-23-2013
    Location
    normal, il
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Challenging complex formula- unique count for multiple criteria on several levels

    @ Max

    Oh ... my... this works!! I will need to examine it a lot closer to fully understand everything that you did... but that is amazing!
    I will need to peck at it for a bit to get it to work for all the data that will be added when it is collected. I will probably end up with thousands of data points.

    But wow- incredible.

    thanks for putting your brain to work on this!

    Joanne

  11. #11
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Challenging complex formula- unique count for multiple criteria on several levels

    Welcome, glad to hear

  12. #12
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Challenging complex formula- unique count for multiple criteria on several levels

    @Joanne,
    - Please note corrections for some earlier mis-alignments just detected (apologies): Steps and formulae have been revised in my post.

+ 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. complex problems count based on many levels of criteria
    By joannelittell in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-28-2013, 04:42 PM
  2. [SOLVED] Challenge for real excel experts!! complex categorization on multiple criteria
    By joannelittell in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-16-2013, 05:07 AM
  3. [SOLVED] Complex macro? or Multiple macros? Manual? CHALLENGE!!
    By kamgrn in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-06-2012, 11:06 AM
  4. [SOLVED] Sum multiple entire rows based on criteria, array formula challenge!
    By ppffffpp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2012, 09:42 PM
  5. Complex multi criteria lookup and count formula???
    By JapanDave in forum Excel General
    Replies: 9
    Last Post: 05-25-2009, 04:36 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1