CountIF to give a count of the Unique vaules using

问题:

My Data is in Three columns

Column A: Cities
Column B: Product Code
Column C: Company name

Now, For Column C, One company can have data in multiple rows, with different cities and Product codes.

The following is required:

Say City= New York
Product Code= 100

For City New York, Code 100, Count the unique number of companies in Column C.

A Total row count is easy to get, but I am confused on how to take the unique count value of the Company Names after those filters.

Thanks.

回答1:

There are two methods, one uses FREQUENCY and the other is an array formula with SUM and COUNTIFS. I prefer the latter:

=SUM(IF(($A$2:$A$7=D2)*($B$2:$B$7=D3),1/COUNTIFS($A$2:$A$7,D2,$B$2:$B$7,D3,$C$2:$C$7,$C$2:$C$7)))

Being an array formula it needs to be entered with Ctrl-Shift-Enter instead of enter when exiting edit mode. If done correctly Excel will put {} around the formula.

回答2:

Here is the frequency one:-

=SUM(--(FREQUENCY(IF((A2:A7=D2)*(B2:B7=D3),MATCH(C2:C7,C2:C7,0)),ROW(C2:C7)-ROW(C2)+1)>0))

(also an array formula) taken from here