Frequency Function with Multiple Criteria

**LilSisKin** · 05-08-2013, 01:38 PM

Hello expert excel users. I need your assistance. I am trying to come up with a formula to count the number of primary vendors within several different categories.

In column G I have the type of category.
In column H I have listed if the vendor is primary or secondary
In column I I have the unique vendor ID

What I need to do is count the number of primary vendors within a category using the unique vendor ID column. I am guessing I need to use the frequency function but I am having issues adding multiple criteria to it.

Thanks in advance for your help.

**oeldere** · 05-08-2013, 02:06 PM

You get better help if you add an excel file, without confidention information.

Please add also the desired (expected) result.

**LilSisKin** · 05-08-2013, 02:47 PM

Attached is a sample file. The result I want to show is:

Carpet Cleaning: 3 unique vendor ID's
Cleaning: 4 Unique vendor ID's
Painting: 18 unique vendor ID's

**oeldere** · 05-08-2013, 02:53 PM

With 2 helpcolums and a pivot table.

See the attached file.

Editing:
notice the result on cleaning is 5 (which is correct, while a blanc vendor is also an unique vendor).

**Tony Valko** · 05-08-2013, 03:00 PM

Try this...

Use cells to hold the criteria:

X2 = Carpet Cleaning
X3 = Cleaning
X4 = Painting

This array formula** entered Y2 and copied down to Y4:

=SUM(IF(FREQUENCY(IF(A$2:A$81=X2,MATCH(C$2:C$81,C$2:C$81,0)),ROW(C$2:C$80)-ROW(C$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.