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?

 

[tra1a]

Hi All-

This is what I've ended up with. It's not working. I am getting zero, though it should be 3. Thoughts?

=SUM(IF(FREQUENCY(IF(C:C="backpacking",IF(D:D="women",E:E)),E:E),1))

 

[T. Valko]

Hi All-

This is what I've ended up with. It's not working. I am getting zero, though it should be 3. Thoughts?

=SUM(IF(FREQUENCY(IF(C:C="backpacking",IF(D:D="women",E:E)),E:E),1))

What type of data is in column E? Is it text? Numbers? Could be both? Something else?

Can you post a few rows worth of sample data and tell us what result you expect?

Unless you have data in every cell in the column you should avoid using entire columns as range references with array formulas.

 

[tra1a]

It is all text

 

[circledchicken]

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

A PivotTable can still be used but you need a helper column. This article explains the implementation process well:
Unique Count in Excel Pivot Table With PowerPivot

For Excel 2010+ however, the easiest method might be PowerPivot as explained in the same article using a measure.

 

[T. Valko]

It is all text

Here's an example.

Book1
	A	B	C	D	E
2	Blue	Fast	Pete	_	3
3	Blue	Fast	Biff	_	_
4	Blue	Fast	Biff	_	_
5	Blue	Fast	Lisa	_	_
6	Blue	Fast	Lisa	_	_
7	White	Fast	Tom	_	_
8	Blue	Slow	Tom	_	_
9	Blue	Slow	Bob	_	_
10	Black	Medium	Sandy	_	_
Sheet1

This array formula** entered in E2:

=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.

 

[Vidar]

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")

Hi Aladin
What is the limit of how many rows you can include in the formula in 2003 version of Excel (and older).

Vidar

 

[Aladin Akyurek]

Hi Aladin
What is the limit of how many rows you can include in the formula in 2003 version of Excel (and older).

Vidar

Hi Vidar

In Excel 2003, the maximum worksheet size is 65536 rows by 256 columns.

While

=COUNTIF(A:A,"X")

=COUNTIF(2:2,"X")

=SUMPRODUCT((2:2="X")+0)

are allowed,

=SUMPRODUCT((A:A="X")+0)

{=SUM(IF(A:A="X",1))}

are not. The whole column restriction is lifted up starting with the 2007 system.

Whole column references affect the efficiency adversely in array processing formulas, so they should be avoided. Such adverse effect does not appear to occur in range processing formulas like one with COUNTIF(S).

 

[Vidar]

Thanks for the reply Aladin

So
=SUMPRODUCT((A:A="X")+0)
is not allowed, but
=SUMPRODUCT((A1:A65536="X")+0)
is allowed for 2003 version and older?

 

[Aladin Akyurek]

Thanks for the reply Aladin

So
=SUMPRODUCT((A:A="X")+0)
is not allowed, but
=SUMPRODUCT((A1:A65536="X")+0)
is allowed for 2003 version and older?

Yes, indeed. but you could switch to dynamic named ranges.

 

[Vidar]

Ok, I understand now. Thank you for the answers.

Vidar