I'm trying to count specific cells in my column of text. The CountA would
work except there are some cells that I need excluded. These cells all begin
with the word "assist" so I tried the following function which isn't given my
the expected results.

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

taking the small example below, I need it to calculate the answer 3 but it's
calculating 4 (4 is answer of just the COUNTA portion so I don't think it's
doing anything with my subtraction and COUNTIF portion)

MA-90
blank
MB-90
MA-90
blank
assist MB-90

This spreadsheet is a grid of sorts so I need the cells with assist to stay
in this column; otherwise, I would separate the data and count again but that
won't work in this case.

Hope this all make sense. Thanks for any assistance you can provide.
Marcia

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

There's nothing wrong with your formula so if the result is incorrect you
may have unseen characters in those cells you think are blank. Are there
formulas in these cells that might return a formula blank ("") ? COUNTA will
count formula blanks.

Try one of these and see what happens:

=COUNTIF(F7:F36," ")-COUNTIF(F7:F36,"assist*")

=SUMPRODUCT(--(LEN(TRIM(F7:F36))0))-COUNTIF(F7:F36,"assist*")


--
Biff
Microsoft Excel MVP


"Office user" wrote in message
...
I'm trying to count specific cells in my column of text. The CountA would
work except there are some cells that I need excluded. These cells all
begin
with the word "assist" so I tried the following function which isn't given
my
the expected results.

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

taking the small example below, I need it to calculate the answer 3 but
it's
calculating 4 (4 is answer of just the COUNTA portion so I don't think
it's
doing anything with my subtraction and COUNTIF portion)

MA-90
blank
MB-90
MA-90
blank
assist MB-90

This spreadsheet is a grid of sorts so I need the cells with assist to
stay
in this column; otherwise, I would separate the data and count again but
that
won't work in this case.

Hope this all make sense. Thanks for any assistance you can provide.
Marcia

Thank you for your help. I tried your other 2 formulas and am still getting
my unexpected results. There are no formulas at all in my blank cells but I
went through each cell again anyway and cleared everything (Edit menu). I'm
guessing at this point it must have to do with my data but I'm not sure where.

Thanks for the help,
Marcia


"T. Valko" wrote:

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

There's nothing wrong with your formula so if the result is incorrect you
may have unseen characters in those cells you think are blank. Are there
formulas in these cells that might return a formula blank ("") ? COUNTA will
count formula blanks.

Try one of these and see what happens:

=COUNTIF(F7:F36," ")-COUNTIF(F7:F36,"assist*")

=SUMPRODUCT(--(LEN(TRIM(F7:F36))0))-COUNTIF(F7:F36,"assist*")


--
Biff
Microsoft Excel MVP


"Office user" wrote in message
...
I'm trying to count specific cells in my column of text. The CountA would
work except there are some cells that I need excluded. These cells all
begin
with the word "assist" so I tried the following function which isn't given
my
the expected results.

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

taking the small example below, I need it to calculate the answer 3 but
it's
calculating 4 (4 is answer of just the COUNTA portion so I don't think
it's
doing anything with my subtraction and COUNTIF portion)

MA-90
blank
MB-90
MA-90
blank
assist MB-90

This spreadsheet is a grid of sorts so I need the cells with assist to
stay
in this column; otherwise, I would separate the data and count again but
that
won't work in this case.

Hope this all make sense. Thanks for any assistance you can provide.
Marcia

Viola! I found it. I went through each cell again and discovered one of
them had a space before my word "assist" so it threw off the COUNTIF. Thanks
again for your help.
Marcia


"T. Valko" wrote:

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

There's nothing wrong with your formula so if the result is incorrect you
may have unseen characters in those cells you think are blank. Are there
formulas in these cells that might return a formula blank ("") ? COUNTA will
count formula blanks.

Try one of these and see what happens:

=COUNTIF(F7:F36," ")-COUNTIF(F7:F36,"assist*")

=SUMPRODUCT(--(LEN(TRIM(F7:F36))0))-COUNTIF(F7:F36,"assist*")


--
Biff
Microsoft Excel MVP


"Office user" wrote in message
...
I'm trying to count specific cells in my column of text. The CountA would
work except there are some cells that I need excluded. These cells all
begin
with the word "assist" so I tried the following function which isn't given
my
the expected results.

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

taking the small example below, I need it to calculate the answer 3 but
it's
calculating 4 (4 is answer of just the COUNTA portion so I don't think
it's
doing anything with my subtraction and COUNTIF portion)

MA-90
blank
MB-90
MA-90
blank
assist MB-90

This spreadsheet is a grid of sorts so I need the cells with assist to
stay
in this column; otherwise, I would separate the data and count again but
that
won't work in this case.

Hope this all make sense. Thanks for any assistance you can provide.
Marcia

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Office user" wrote in message
...
Viola! I found it. I went through each cell again and discovered one of
them had a space before my word "assist" so it threw off the COUNTIF.
Thanks
again for your help.
Marcia


"T. Valko" wrote:

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

There's nothing wrong with your formula so if the result is incorrect you
may have unseen characters in those cells you think are blank. Are there
formulas in these cells that might return a formula blank ("") ? COUNTA
will
count formula blanks.

Try one of these and see what happens:

=COUNTIF(F7:F36," ")-COUNTIF(F7:F36,"assist*")

=SUMPRODUCT(--(LEN(TRIM(F7:F36))0))-COUNTIF(F7:F36,"assist*")


--
Biff
Microsoft Excel MVP


"Office user" wrote in message
...
I'm trying to count specific cells in my column of text. The CountA
would
work except there are some cells that I need excluded. These cells all
begin
with the word "assist" so I tried the following function which isn't
given
my
the expected results.

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

taking the small example below, I need it to calculate the answer 3 but
it's
calculating 4 (4 is answer of just the COUNTA portion so I don't think
it's
doing anything with my subtraction and COUNTIF portion)

MA-90
blank
MB-90
MA-90
blank
assist MB-90

This spreadsheet is a grid of sorts so I need the cells with assist to
stay
in this column; otherwise, I would separate the data and count again
but
that
won't work in this case.

Hope this all make sense. Thanks for any assistance you can provide.
Marcia

Hi,

In 2007 you could use

=COUNTIFS(F7:F36,"<assist*",F7:F36,"<")

In 2003 you can use

=SUMPRODUCT(--(F7:F36<""))-COUNTIF(F7:F36,"assist*")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Office user" wrote:

I'm trying to count specific cells in my column of text. The CountA would
work except there are some cells that I need excluded. These cells all begin
with the word "assist" so I tried the following function which isn't given my
the expected results.

=COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")

taking the small example below, I need it to calculate the answer 3 but it's
calculating 4 (4 is answer of just the COUNTA portion so I don't think it's
doing anything with my subtraction and COUNTIF portion)

MA-90
blank
MB-90
MA-90
blank
assist MB-90

This spreadsheet is a grid of sorts so I need the cells with assist to stay
in this column; otherwise, I would separate the data and count again but that
won't work in this case.

Hope this all make sense. Thanks for any assistance you can provide.
Marcia