Count unique text values with Multiple criteria

Bselan

New Member
Joined
Aug 22, 2013
Messages
3
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
    Example 1.PNG
    25.9 KB · Views: 9
  • Example 2.PNG
    Example 2.PNG
    20.5 KB · Views: 9

Some videos you may like

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
Joined
Dec 30, 2008
Messages
10,970
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,787
Messages
5,542,511
Members
410,559
Latest member
jordansmith6532
Top