+ Reply to Thread
Results 1 to 12 of 12

Challenge for real excel experts!! complex categorization on multiple criteria

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

    Challenge for real excel experts!! complex categorization on multiple criteria

    Hi,
    i think i almost have this.... but i am getting a #NAME? error.

    The data categorization is pretty complicated.

    need a unique count for:

    Feedback Category by ID & Type by month/year

    and

    Feedback Category by ID & Type & Color by month/year


    i have attached a spreadsheet with some data. it contains what i have so far--- which is an error that i cant figure out (feel free to ignore).

    and the tables with what the actual data should be.

    Please no pivot tables.


    thanks in advance- this is a tough one.

    Joanne
    Attached Files Attached Files

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    Hi,

    Well, the #NAME? error comes from this part:

    ...SUM(IF(FREQUENCY,IF(...

    Should be:

    ...SUM(IF(FREQUENCY(IF(...

    Regards
    Click * below if this answer helped

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

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    The NAME error is on account of

    SUM(IF(FREQUENCY,IF

    In AD7
    =SUM(FREQUENCY(IF($R$5:$R$224<>"",IF($P$5:$P$224="AAAA001",IF($R$5:$R$224<>"No input required at this time.",IF($R$5:$R$224<>"No feedback is needed.",IF($L$5:$L$224="CP1",IF(TEXT($M$5:$M$224,"mmm yyyy")=$AC7,MATCH($Y$5:$Y$224,$Y$5:$Y$224))))))),ROW($C$5:$C224)-ROW($C$4)+1),1)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

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

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    Awesome. that fixed the error.... now im not getting the correct numbers. Can anyone lend a hand?
    Attached Files Attached Files

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    So can you explain a bit how you arrive at your "What I need..." totals?

    Or, put another way, which of the criteria in the current formulas that you're using are now redundant?

    Regards

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

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    i put in an explanation within the spreadsheet.... hopefully it makes sense.

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    Hi,

    I'm sorry but it's still a little confusing as some of your statements don't seem consistent.

    In your first post, you say you want a unique count for Feedback Category by ID & Type by month/year and Feedback Category by ID & Type & Color by month/year.

    The first of these is consistent with the notes in your attachment: "even though there is varying feedback (7 total) there is only one ID & Type (R200van)... So that only counts as 1."

    However, for the second, your notes say:

    "For R200vanblue there are 3 different feedbacks (1003, 105 and 1300). That gives the blue cell value" and

    "For R200vangreen there are 2 different feedbacks (1004 and 18888). NOTE "No input required at this time." and "No feedback is needed." are NOT COUNTED. That gives the green cell value."

    but your original statement does not call for a unique count taken to the level of different feedbacks (Feedback Category, yes, but this is not the same thing).

    It seems as if you are saying that varying feedback should be considered for the count of Feedback Category by ID & Type & Color by month/year but not for Feedback Category by ID & Type by month/year?

    There are also several small errors in the formulas you currently have - can I ask if you wrote them yourself? Can you also confirm whether the Feedback categories "No input required at this time." and "No feedback is needed." are still to be excluded from the count?

    In the meantime, this, I think, should be your corrected formula (in cell AD7 and to copy across and down) for your first unique count. If you could check this and also clarify my queries above re the second.

    =SUM(IF(FREQUENCY(IF($R$5:$R$225<>"",IF($P$5:$P$225="AAAA001",IF($R$5:$R$225<>"No input required at this time.",IF($R$5:$R$225<>"No feedback is needed.",IF($L$5:$L$225=AD$6,IF(TEXT($M$5:$M$225,"mmm yyyy")=$AC7,MATCH($Y$5:$Y$225,$Y$5:$Y$225,0))))))),ROW($C$5:$C$225)-ROW($C$5)+1),1))

    Regards

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

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    Hi,

    I apologize for providing incorrect information. i corrected it in the new spreadsheet. Unfortunately, since i was not clear, i led you astray. yes, i did write this myself.

    I was able to get the formula to almost get what i need. i have been able to get it to display the correct number of actual feedback....but my MATCH is not working correctly.
    Attached Files Attached Files

  9. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    Hi,

    Sorry but it's still not clear, and you didn't specifically anwer my questions. From what I understand then, from these two statements (from your new sheet):

    For ID & Type: "even though there is varying feedback (5 total) there is only one ID & Type (R200van)... So that only counts as 1."

    For ID & Type & Color: "For R200vanblue there are 3 different feedbacks (1003, 105 and 1300)."

    for the former you want a unique count of ID & Type but for the latter a unique count of Feedback (I would have thought this should be a unique count of ID & Type & Color?)

    Can you please clarify? (Would prefer explanations in the thread rather than in new attachments if that's ok.)

    Also - did you get chance to test my solution for the first table?

    Regards

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

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    Quote Originally Posted by XOR LX View Post
    Also - did you get chance to test my solution for the first table?
    Yes, however i provided incorrect information and it was not what i needed.


    Quote Originally Posted by XOR LX View Post
    For ID & Type: "even though there is varying feedback (5 total) there is only one ID & Type (R200van)... So that only counts as 1."

    For ID & Type & Color: "For R200vanblue there are 3 different feedbacks (1003, 105 and 1300)."

    for the former you want a unique count of ID & Type but for the latter a unique count of Feedback (I would have thought this should be a unique count of ID & Type & Color?)
    you are completely correct! i have been working on this for so long, i am making mistakes.
    Yes, i need a unique count of ID & Type & Color.

    Thank you for taking so much time to dig into this mess.
    Joanne

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

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    OK, your solution did work.... i was comparing it with MY incorrect information.

    You are a wonderful, patient and knowledgeable person.

    Thank you so very much!!!!

    Joanne Littell

  12. #12
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Challenge for real excel experts!! complex categorization on multiple criteria

    You're welcome!

    And thanks for the very kind words - it's much appreciated.

    All the best with the project, and get back to me if you need any more help with this.

    Regards

+ 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. Challenge for experts - full up the matrix
    By sadiqueakhter in forum Excel General
    Replies: 1
    Last Post: 02-07-2013, 04:05 AM
  2. [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
  3. Need help from the real world experts in Excel
    By Atheria in forum Excel General
    Replies: 1
    Last Post: 10-10-2006, 06:46 AM
  4. [SOLVED] A challenge for the Excel Experts among us (ActiveX Control & Add-ins)
    By JMMach in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2006, 09:35 AM
  5. Replies: 2
    Last Post: 11-10-2005, 12:00 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