Search code examples
excelif-statementcountduplicates

COUNTIFS in a spreadsheet while ignoring duplicates in a separate column

I'd like to find how to use the COUNTIFS function in excel to count the rows in a spreadsheet while ignoring duplicate values in a separate column. Here is a sample of the data:

      [Sample data in pastable format below]

I would like to count the number of rows that are "One" on status and "Blue" on category. However, I only want to count the duplicates for these respective values in "ID" once.

Here's what I've tried:

=SUM(IF(FREQUENCY($A$2:$A$12,$A$2:$A$12)>0,1))

Same problem as the previous example. - this works for giving me the number of duplicates in column A, but I can't work any IF statements in effectively.

=IF(B2:B12="One",IF(C2:C12="Blue",SUM(IF(FREQUENCY($A$2:$A$12,$A$2:$A$12)>0,1))))

When I add IF statements in, I get the same number as the earlier formula.

For your copy/pasting pleasure:

ID     Status  Category
1423    One     Blue
1423    One     Blue
1423    One     Red
5124    One     Blue
5124    One     Blue
2341    Two     Blue
1111    One     Red
1212    Two     Red
1212    One     Blue
1111    One     Red
5124    One     Red

The ideal result would be 3.

Solution

  • There is a standard COUNTUNIQUE method using SUMPRODUCT and COUNTIF functions; (e.g. SUMPRODUCT(1/COUNTIF(A2:A12, A2:A12&"")) ). This can be expanded to include conditions by changing to a COUNTIFS function but care must be made to ensure that no #DIV/0! error can occur.

    In E4 as a standard formula,

    =SUMPRODUCT(((B2:B12="one")*(C2:C12="blue"))/(COUNTIFS(A2:A12, A2:A12, B2:B12, "one", C2:C12, "blue")+((B2:B12<>"one")+(C2:C12<>"blue"))))

    The numerator of the division operation provides half of the conditional processing. The COUNTIFS denominator of the division operator must be the opposite (non-zero) of the numerator when the numerator is zero.

          countunnique_blue_red