CountIF or COUNTA for specific text

[Office user]

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

 

[T. Valko]

=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*")

 

[Office user]

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

 

[Office user]

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

 

[Shane Devenshire]

Hi,

In 2007 you could use

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

In 2003 you can use

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

 

[T. Valko]

Good deal. Thanks for the feedback!

 

[anying fiona]

i want to create attendance list for different employees in my company and give them ticks to monitor their presence or absence and be able to count at the end of the month in excel