COUNTIF
 

I am putting together a spreadsheet where I need Excel to count both when a
class is scheduled and when a grade is entered. When the class is scheduled
the cell will contain a number designanting the term like 101. Later when
the student recieves a grade the cell would contain the number and the letter
grade, like 101A. How do I get Excel to count the cell under either
condition. I would like it to count if there is either a number only or a
number and a letter but NOT just a letter. Thank you!

COUNTIF
 

So, a cell will contain either a number, a number combined with a letter or
a letter?

Is the number *always* a 3 digit number? Is the letter *always* a single
letter? Do you want to count a range of cells or is this just supposed to
check a single cell?

--
Biff
Microsoft Excel MVP


"PatrickP" wrote in message
...
I am putting together a spreadsheet where I need Excel to count both when a
class is scheduled and when a grade is entered. When the class is
scheduled
the cell will contain a number designanting the term like 101. Later when
the student recieves a grade the cell would contain the number and the
letter
grade, like 101A. How do I get Excel to count the cell under either
condition. I would like it to count if there is either a number only or a
number and a letter but NOT just a letter. Thank you!

COUNTIF
 

Hi Patrick,

you could try something like this

=COUNTA(B1:B21)-COUNTIF(B1:B21,"=A")-COUNTIF(B1:B21,"GG")

This will count all the non blank cells and then minus the count of cells
which are equal or greater than A and less than or equal to GG

Change the A and the GG to suit your letters.
Check as I think that this is case sensitive
Change the range to suit


"PatrickP" wrote:

I am putting together a spreadsheet where I need Excel to count both when a
class is scheduled and when a grade is entered. When the class is scheduled
the cell will contain a number designanting the term like 101. Later when
the student recieves a grade the cell would contain the number and the letter
grade, like 101A. How do I get Excel to count the cell under either
condition. I would like it to count if there is either a number only or a
number and a letter but NOT just a letter. Thank you!

COUNTIF
 

The cell would contain either a 3 digit number OR a 3 digit number with a
letter. For example, 101 or 101A. I want either to be counted and I want it
to count a range of cells. Thanks.

"T. Valko" wrote:

So, a cell will contain either a number, a number combined with a letter or
a letter?

Is the number *always* a 3 digit number? Is the letter *always* a single
letter? Do you want to count a range of cells or is this just supposed to
check a single cell?

--
Biff
Microsoft Excel MVP


"PatrickP" wrote in message
...
I am putting together a spreadsheet where I need Excel to count both when a
class is scheduled and when a grade is entered. When the class is
scheduled
the cell will contain a number designanting the term like 101. Later when
the student recieves a grade the cell would contain the number and the
letter
grade, like 101A. How do I get Excel to count the cell under either
condition. I would like it to count if there is either a number only or a
number and a letter but NOT just a letter. Thank you!


.

COUNTIF
 

Winnie: Is COUNTA telling Excel to "count all?"

"winnie123" wrote:

Hi Patrick,

you could try something like this

=COUNTA(B1:B21)-COUNTIF(B1:B21,"=A")-COUNTIF(B1:B21,"GG")

This will count all the non blank cells and then minus the count of cells
which are equal or greater than A and less than or equal to GG

Change the A and the GG to suit your letters.
Check as I think that this is case sensitive
Change the range to suit


"PatrickP" wrote:

I am putting together a spreadsheet where I need Excel to count both when a
class is scheduled and when a grade is entered. When the class is scheduled
the cell will contain a number designanting the term like 101. Later when
the student recieves a grade the cell would contain the number and the letter
grade, like 101A. How do I get Excel to count the cell under either
condition. I would like it to count if there is either a number only or a
number and a letter but NOT just a letter. Thank you!

COUNTIF
 

Hi Patrick,

Yes COUNTA counts all nonblank cells within the range

"PatrickP" wrote:

Winnie: Is COUNTA telling Excel to "count all?"

"winnie123" wrote:

Hi Patrick,

you could try something like this

=COUNTA(B1:B21)-COUNTIF(B1:B21,"=A")-COUNTIF(B1:B21,"GG")

This will count all the non blank cells and then minus the count of cells
which are equal or greater than A and less than or equal to GG

Change the A and the GG to suit your letters.
Check as I think that this is case sensitive
Change the range to suit


"PatrickP" wrote:

I am putting together a spreadsheet where I need Excel to count both when a
class is scheduled and when a grade is entered. When the class is scheduled
the cell will contain a number designanting the term like 101. Later when
the student recieves a grade the cell would contain the number and the letter
grade, like 101A. How do I get Excel to count the cell under either
condition. I would like it to count if there is either a number only or a
number and a letter but NOT just a letter. Thank you!

COUNTIF
 

That is all cells with data

"winnie123" wrote:

Hi Patrick,

Yes COUNTA counts all nonblank cells within the range

"PatrickP" wrote:

Winnie: Is COUNTA telling Excel to "count all?"

"winnie123" wrote:

Hi Patrick,

you could try something like this

=COUNTA(B1:B21)-COUNTIF(B1:B21,"=A")-COUNTIF(B1:B21,"GG")

This will count all the non blank cells and then minus the count of cells
which are equal or greater than A and less than or equal to GG

Change the A and the GG to suit your letters.
Check as I think that this is case sensitive
Change the range to suit


"PatrickP" wrote:

I am putting together a spreadsheet where I need Excel to count both when a
class is scheduled and when a grade is entered. When the class is scheduled
the cell will contain a number designanting the term like 101. Later when
the student recieves a grade the cell would contain the number and the letter
grade, like 101A. How do I get Excel to count the cell under either
condition. I would like it to count if there is either a number only or a
number and a letter but NOT just a letter. Thank you!

COUNTIF
 

I would like it to count if there is either a number only
or a number and a letter but NOT just a letter.
The cell would contain either a 3 digit number
OR a 3 digit number with a letter.

Try this...

=SUMPRODUCT(--(LEN(A1:A10)1))

--
Biff
Microsoft Excel MVP


"PatrickP" wrote in message
...
The cell would contain either a 3 digit number OR a 3 digit number with a
letter. For example, 101 or 101A. I want either to be counted and I want
it
to count a range of cells. Thanks.

"T. Valko" wrote:

So, a cell will contain either a number, a number combined with a letter
or
a letter?

Is the number *always* a 3 digit number? Is the letter *always* a single
letter? Do you want to count a range of cells or is this just supposed to
check a single cell?

--
Biff
Microsoft Excel MVP


"PatrickP" wrote in message
...
I am putting together a spreadsheet where I need Excel to count both
when a
class is scheduled and when a grade is entered. When the class is
scheduled
the cell will contain a number designanting the term like 101. Later
when
the student recieves a grade the cell would contain the number and the
letter
grade, like 101A. How do I get Excel to count the cell under either
condition. I would like it to count if there is either a number only
or a
number and a letter but NOT just a letter. Thank you!


.