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

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.

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

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

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

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