Counting in an array
 

I have a 700 line spreadsheet, 2 columns). Column A has is a name (it is
concatenated from elsewhere) and column B is a value (A or B).

I made a list of all the unique names from the first column and placed it in
Column E. I would like to be able to look at the list of 700 and cound the
number of "A"s and "B"s

CarA A
CarB B
CarA B
CarA A

Here is what I tried:
=COUNT(IF((A$2:$A$701=$S13)*($F$2:$F$701="A"),$F$2 :$F$701))

It doesn't work, I get 0's

Counting in an array
 

The A's and B's are in column B, right?

=COUNTIF(B2:B701,"A")

and

=COUNTIF(B2:B701,"B")

Rick


"PAL" wrote in message
...
I have a 700 line spreadsheet, 2 columns). Column A has is a name (it is
concatenated from elsewhere) and column B is a value (A or B).

I made a list of all the unique names from the first column and placed it
in
Column E. I would like to be able to look at the list of 700 and cound
the
number of "A"s and "B"s

CarA A
CarB B
CarA B
CarA A

Here is what I tried:
=COUNT(IF((A$2:$A$701=$S13)*($F$2:$F$701="A"),$F$2 :$F$701))

It doesn't work, I get 0's

Counting in an array
 

This will get you the number of A's:

=COUNTIF(F2:F701,"A")

and for the B's:

=COUNTIF(F2:F701,"B")

Hope this helps.

Pete

On Oct 29, 7:16 pm, PAL wrote:
I have a 700 line spreadsheet, 2 columns). Column A has is a name (it is
concatenated from elsewhere) and column B is a value (A or B).

I made a list of all the unique names from the first column and placed it in
Column E. I would like to be able to look at the list of 700 and cound the
number of "A"s and "B"s

CarA A
CarB B
CarA B
CarA A

Here is what I tried:
=COUNT(IF((A$2:$A$701=$S13)*($F$2:$F$701="A"),$F$2 :$F$701))

It doesn't work, I get 0's

Counting in an array
 

You are very confusing, you say you put the unique names in E while your
formula does not reference E anywhere so what does those names has to do
with anything? You might want to read up on SUMPRODUCT here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


=SUMPRODUCT(--(A$2:$A$701=$S13),--($F$2:$F$701="A"))

will count occurrences of A in column F where column A is equal to S13


--


Regards,


Peo Sjoblom




"PAL" wrote in message
...
I have a 700 line spreadsheet, 2 columns). Column A has is a name (it is
concatenated from elsewhere) and column B is a value (A or B).

I made a list of all the unique names from the first column and placed it
in
Column E. I would like to be able to look at the list of 700 and cound
the
number of "A"s and "B"s

CarA A
CarB B
CarA B
CarA A

Here is what I tried:
=COUNT(IF((A$2:$A$701=$S13)*($F$2:$F$701="A"),$F$2 :$F$701))

It doesn't work, I get 0's

Counting in an array
 

Peo,

You are right, I mislabled the field and put in an "E" in my message. In
any event, your guidance was spot on and it works great. Thanks.

"Peo Sjoblom" wrote:

You are very confusing, you say you put the unique names in E while your
formula does not reference E anywhere so what does those names has to do
with anything? You might want to read up on SUMPRODUCT here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


=SUMPRODUCT(--(A$2:$A$701=$S13),--($F$2:$F$701="A"))

will count occurrences of A in column F where column A is equal to S13


--


Regards,


Peo Sjoblom




"PAL" wrote in message
...
I have a 700 line spreadsheet, 2 columns). Column A has is a name (it is
concatenated from elsewhere) and column B is a value (A or B).

I made a list of all the unique names from the first column and placed it
in
Column E. I would like to be able to look at the list of 700 and cound
the
number of "A"s and "B"s

CarA A
CarB B
CarA B
CarA A

Here is what I tried:
=COUNT(IF((A$2:$A$701=$S13)*($F$2:$F$701="A"),$F$2 :$F$701))

It doesn't work, I get 0's

Counting in an array
 

If you want the counts of A's and B's for each person listed in column A, you
can also create a Pivot table. The list must have a header row, say "Name" and
"Code"

Use Name as the row field, Code as the column field and either Name or Code as
the data field. Excel will automatically do a count, since the data is text.

On Mon, 29 Oct 2007 12:16:01 -0700, PAL wrote:

I have a 700 line spreadsheet, 2 columns). Column A has is a name (it is
concatenated from elsewhere) and column B is a value (A or B).

I made a list of all the unique names from the first column and placed it in
Column E. I would like to be able to look at the list of 700 and cound the
number of "A"s and "B"s

CarA A
CarB B
CarA B
CarA A

Here is what I tried:
=COUNT(IF((A$2:$A$701=$S13)*($F$2:$F$701="A"),$F$ 2:$F$701))

It doesn't work, I get 0's