# Count unique text values with Multiple criteria

#### Bselan

##### New Member
Hello,

I have a data set that has Materials (column A) assigned to a product group (column E), product groups could be part of multiple Product Specification (PS, column G) and these items are built in multiple configuration (column C)

I am trying to count the total number of unique configurations (C) for a given Product group (e) and Product spec (G); counting text values vs numeric values...

I am currently using (column D) =SUMPRODUCT(--(FREQUENCY(IF(\$E\$5:\$E\$7278=E5,IF(\$G\$5:\$G\$7278=G5,MATCH(\$H\$5:\$H\$7278,\$H\$5:\$H\$7278,0))),ROW(\$H\$5:\$H\$7278)-ROW(H5)+1)>0))

which works most of the time...
When filtering on column E for ACCESSORIES GLOBAL & column G PS1000183, it returns 1 which is correct
When filtering on column E for ACCESSORIES GLOBAL & column G PS1000184, it returns 2 which is correct
When filtering on column E for SH MITRACLIP NT CE & column G PS1014342, some rows return 2 (correct) some rows return 1 (incorrect)

Is there a row limitation on this formula, will it not calculate 7278 rows?

Not sure how to share the file but I can give two screen shots of my issue

Any help would be appreciated!!!

Thank you!

#### Attachments

• Example 1.PNG
25.9 KB · Views: 9
• Example 2.PNG
20.5 KB · Views: 9

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### jasonb75

##### Well-known Member
The only anomaly that I can see is the last row reference being relative instead of absolute

=SUMPRODUCT(--(FREQUENCY(IF(\$E\$5:\$E\$7278=E5,IF(\$G\$5:\$G\$7278=G5,MATCH(\$H\$5:\$H\$7278,\$H\$5:\$H\$7278,0))),ROW(\$H\$5:\$H\$7278)-ROW(\$H\$5)+1)>0))

With a relative reference at that point the bins array of frequency would not be aligned with the data array as expected.

Replies
5
Views
137
Replies
8
Views
183
Replies
8
Views
249
Replies
2
Views
39
Replies
8
Views
178