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

1. 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.

thanks in advance- this is a tough one.

Joanne

2. 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

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

The NAME error is on account of

SUM(IF(FREQUENCY,IF

=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.

4. 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?

5. 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. Re: Challenge for real excel experts!! complex categorization on multiple criteria

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

7. 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. 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.

9. 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. Re: Challenge for real excel experts!! complex categorization on multiple criteria

Originally Posted by XOR LX
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.

Originally Posted by XOR LX
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. 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. 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

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

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