Criteria for Countif

[coa01gsb]

Hi All,

I am a little confused as to what can be entered into the criteria par
of a countif statement.

For example if i wanted to count all the cells in column A that onl
contain one character how could I do it just using the counti
statement?

Or if I wanted to count all the cells in column A that exactly matche
the entry on the same row in column B?

Any ideas

 

[Special-K]

Here's what I'd do

Enter a formula in B1

=LEN(A1)

and copy it down the column

then in another column

=COUNTIF(B1:B100,1)

Counts number of times 1 occurs between B1 and B100 hence number of
occurrences of 1 character cell in column A


Similarly

in C1

=IF(A1=B1,1,0)

copy the formula down column C
then in another column

=COUNTIF(C1:C100,1)

Counts number of times 1 occurs between C1 and C100 hence number of
times a cell in A corresponds with a cell in B on the same row

 

[Guest]

=SUMPRODUCT(--(LEN(A2:A11)=1))
Adjust range as required!

Regards,
Stefi


„coa01gsb†ezt írta:

 

[coa01gsb]

Cheers for the response special-k, had managed to get that far an
realise you can hide the columns and such but was keen to find a wa
simply using one statement.

Which Stefi rightly got. I'll read up a little on the sumproduc
statement, but for now can you tell me what exactly the '--' i
required for? Never seen that syntax used before just wondered what i
does in case it's useful in other function

 

[David Biddulph]

message ....

.... I'll read up a little on the sumproduct
statement, but for now can you tell me what exactly the '--' is
required for? Never seen that syntax used before just wondered what it
does in case it's useful in other functions

The double unary minus forces the values to be numeric, so is useful if you
are starting with values which are logical (TRUE/FALSE) or text.

The first unary minus reverses the sign, and the second reverses it again.
http://www.mcgimpsey.com/excel/variablerate1.html gives an example.