COUNT func

D

DollarBill

I have tried this function several times to achieve a goal but it is not
working the way I would like it to.

Here's what I have.

A sheet with a column of text that represents levels of certification, i.e.
a column may have "1, 2, a1, a2, ...". The column may have no certs ro it
may have many. I want to count the number of cells that contain a 1 or 2,
etc.. When I use the various COUNT functions, the result is incorrect. I
want to assure that the count doesn't include those strings that are say, a1
or a2, just the 2. Finally, is there a way of counting the number of cells
that two specified string values?

Please advise and thank you.

-Bill
 
B

Bernard Liengme

=COUNT(A1:A40) will report how many of the cells in that range have numeric
values.
Tell us what formula you used and what result you get.
=COUNTIF(A1:A40,1) will count how many cells in the range have a value of 1
=COUNTIF(A1:A40,"a1") + COUNTIF(A1:A40, "a2") will count how many cells have
value a1 plus how many have value a2
best wishes
 
P

Paul B

Bill, will this work,

=COUNTIF(A1:A10,2)

=COUNTIF(A1:A10,"A2")

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
D

DollarBill

Paul B said:
Bill, will this work,

=COUNTIF(A1:A10,2)

=COUNTIF(A1:A10,"A2")
Click to expand...

Neither of these are working. I should have mentioned that this data is
text, not numeric. The certs can be either numeric or text but there is no
operation on them so they are defined as text. I can manually sort them and
count by hand but would be easier to have a function call for these as there
are about 20 sheets I need to use to calculate a trend.

When I use either of these with the A2 either by itself or in quotes, an
incorrect value is returned.

Thanks,
Bill
 
D

DollarBill

DollarBill said:
Neither of these are working. I should have mentioned that this data is
text, not numeric. The certs can be either numeric or text but there is
no operation on them so they are defined as text. I can manually sort
them and count by hand but would be easier to have a function call for
these as there are about 20 sheets I need to use to calculate a trend.

When I use either of these with the A2 either by itself or in quotes, an
incorrect value is returned.

Thanks,
Bill
Click to expand...

I think the problem is that there are multiple values in each cell. Like, 1
2 3 4A 5A 6B ABC, etc. This is just a text string. I want to count each
occurrence of a particular value within that string.
 
D

DollarBill

DollarBill said:
Neither of these are working. I should have mentioned that this data is
text, not numeric. The certs can be either numeric or text but there is
no operation on them so they are defined as text. I can manually sort
them and count by hand but would be easier to have a function call for
these as there are about 20 sheets I need to use to calculate a trend.

When I use either of these with the A2 either by itself or in quotes, an
incorrect value is returned.
Click to expand...

Yep, that's the problem. If I put "1 2 3" into the function call, it will
tell me how many people have a "1 2 3" as their cert. However, it will not
pick out how many have a 1 in their list of certs. Writing a count function
for every combination of certifications would be more time-consuming than
calculating by hand.

Any suggestions?
 
B

Bernard Liengme

I have some dummy data in A1:A10 ( 1 2 3 a1/ 1 2 3/ a1 1 2/ 1 2 a1/ etc)
In B1 I have the certificate I am looking for (say a1)
In C1 I have the formula
=SUMPRODUCT(--(SUBSTITUTE($A$1:$A$5,B1,"")<>$A$1:$A$5))
and it correctly tells me that there are three cells with 'a1' somewhere in
them
best wishes
 
D

DollarBill

Bernard Liengme said:
I have some dummy data in A1:A10 ( 1 2 3 a1/ 1 2 3/ a1 1 2/ 1 2 a1/ etc)
In B1 I have the certificate I am looking for (say a1)
In C1 I have the formula
=SUMPRODUCT(--(SUBSTITUTE($A$1:$A$5,B1,"")<>$A$1:$A$5))
and it correctly tells me that there are three cells with 'a1' somewhere
in them
best wishes
Click to expand...

Wow. That's pretty complicated. I'll attempt to "learn me a little" about
the sumproduct and substitute functions to try to make sense of it all.
Seems like there should be some sort of built-in function call to perform
this operation.

Thanks,
Bill
 
B

Bernard Liengme

I cannot agree about the need for a built in function. The main problem is
the design of you data. Normally one keeps one piece of data in a cell. So
the best layout would be to have a series of cells with the separate data
(1's , 2's, etc). You could use Data|Text to Column to do this to your
data.
best wishes
 
D

DollarBill

Bernard Liengme said:
I cannot agree about the need for a built in function. The main problem is
the design of you data. Normally one keeps one piece of data in a cell. So
the best layout would be to have a series of cells with the separate data
(1's , 2's, etc). You could use Data|Text to Column to do this to your
data.
best wishes
Click to expand...

Ah hah. I will try the Data:Text to Column. That would seem to be a better
route anyway because there are some other counting things I want to do with
the data as well and having the certs in columns would facilitate that.
Thanks for the suggestion.

-Bill
 
Top