Count Unique values based on criteria within 2 columns

worksheet-function

I hope someone out there has the brains to help me with issue.

I have a spreadsheet that has multiple columns and duplicates. What i want to do is count all the unique values in column A, only if Column B and C equal unique text specified. I have outlined an example below:

Column A    Column B       Column C
24          Unit a          Type A
24          Unit a          Type A
26          Unit C          Type A
25          Unit A          TYpe A

So using the above as an example the end result if counting unique numbers in column A, that are in Unit A (Column B) and Type A(Column C) = 2.

Im pretty sure I need to utilised sum, frequency and match but have not been able to it to yeild the correct results.

Im using Excel 2010

Best Answer

an easy hack to achieve this is to use Column D:

= Concatenate(Column B, "###", Column C)

then you can do CountIf(ColumnD = TheValue)

Related Question