Counting in an array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
The A's and B's are in column B, right?

=COUNTIF(B2:B701,"A")

and

=COUNTIF(B2:B701,"B")

Rick
 
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
 
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
 
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.
 
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.
 
Back
Top