Count if AND then Unique

[tra1a]

I have three columns: A, B, C

I want to tally a unique record in column C as it relates to A and B.

example: Number of times Boat appears in column C if A is blue and B is fast.

Any thoughts?

 

[Vidar]

This one might work...
=SUMPRODUCT(--(A:A="blue"),--(B:B="fast"),--(C:C="Boat"))

 

[circledchicken]

Hi,

I think a PivotTable would also be an easy, flexible option. For an introduction on how to use them see:

• Overview of PivotTable and PivotChart reports - Excel - Office.com
• Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
• Excel Pivot Table -- Dynamic Data Source

If you have problems implementing it in your case, perhaps post back with any specific problem areas.

 

[T. Valko]

I have three columns: A, B, C

I want to tally a unique record in column C as it relates to A and B.

example: Number of times Boat appears in column C if A is blue and B is fast.

Any thoughts?

What version of Excel are you using?

 

[Aladin Akyurek]

I have three columns: A, B, C

I want to tally a unique record in column C as it relates to A and B.

example: Number of times Boat appears in column C if A is blue and B is fast.

Any thoughts?

Vidar appears to assume a 2007 system or later. If thta is correct, we can also add a COUNTIFS formula to the arsenal next to Sumproduct and a pivot table:

=COUNTIFS(A:A,"blue",B:B,"fast",C:C,"Boat")

 

[T. Valko]

Vidar appears to assume a 2007 system or later. If thta is correct, we can also add a COUNTIFS formula to the arsenal next to Sumproduct and a pivot table:

=SOUNTIFS(A:A,"blue",B:B,"fast",C:C,"Boat")

Looks like an error in your formula.

 

[tra1a]

I didn't explain correctly the first time. I agree about the pivot table, but it won't work for this purpose.

So if A is Blue and B is Fast, I need to know how many uniques are C

 

[T. Valko]

I didn't explain correctly the first time. I agree about the pivot table, but it won't work for this purpose.

So if A is Blue and B is Fast, I need to know how many uniques are C

Try this array formula**:

=SUM(IF(FREQUENCY(IF(A2:A10="Blue",IF(B2:B10="Fast",MATCH(C2:C10,C2:C10,0))),ROW(C2:C10)-ROW(C2)+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.

 

[Aladin Akyurek]

I didn't explain correctly the first time. I agree about the pivot table, but it won't work for this purpose.

So if A is Blue and B is Fast, I need to know how many uniques are C

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(C2:C400<>"",
  IF(A2:A400="Blue",IF(B2:B400="Fast",
   MATCH("~"&C2:C400,C2:C400&"",0)))),ROW(C2:C400)-ROW(C2)+1),1))

If there are no surrounding chars with special meaning like < around the target entries, the "~"& and &"" bits can be removed from the formula.

 

[T. Valko]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(A2:A10="Blue",IF(B2:B10="Fast",MATCH(C2:C10,C2:C10,0))),ROW(C2:C10)-ROW(C2)+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.

If the data in column C is numbers then we can use this shorter version:

=SUM(IF(FREQUENCY(IF(A2:A10="Blue",IF(B2:B10="Fast",C2:C10)),C2:C10),1))

Still array entered!