COUNTIF formula multiple text exclusions

[evilfeevil@gmail.com]

I have a worksheet with a column that counts using COUNTA, everything
in a cell - text, numbers, all nonblank cells. But I don't want it to
count the occurrence of any of this text: "NA", "N/A", or "DI" (I do
want it to count all dates, the word "COMPLETED", and possibly other
data in the future.). Right now the formula is only excluding
occurrences of "NA" from the count:

=(COUNTA(H11:H77)-COUNTIF(H11:H77,"NA"))

How do I tell it to exclude counting either "NA", OR "N/A" OR "DI"? I
tried putting a comma after "NA", then putting "N/A", etc, but it
didn't work (neither did semicolons), like this:

=(COUNTA(H11:H77)-COUNTIF(H11:H77,"NA","N/A","DI"))

For now I've put in three separate COUNTIF statements, like this:

=(COUNTA(I11:I77)-COUNTIF(I11:I77,"NA")-COUNTIF(I11:I77,"N/A")-COUNTIF(I11:I77,"DI"))

....and it works, but it's so long, it looks sloppy and confusing and if
I want to add more criteria in the future, I don't want to make this
formula a mile long. Isn't there an easier way to do it? I guess my
main question is, is there somewhere I can see a list of the many
formula calculation operators in Excel, especially the criteria
separator(s)? I found a list of the different operators and what they
do in the Excel Help menu, but I still can't find the answer or an
example of how to put my text exclusions together in the formula.
Thanks.

[SteveG]

You could use this.

=SUMPRODUCT((A1:A100<>"N/A")*(A1:A100<>"NA")*(A1:A100<>"DI")*(A1:A100<>""))

HTH

Steve

[Domenic]

Try...

=COUNTA(H11:H77)-SUM(COUNTIF(H11:H77,{"NA","N/A","DI"}))

or

=COUNTA(H11:H77)-SUM(COUNTIF(H11:H77,A2:A4))

....where A2:A4 contains the values to exclude.

Hope this helps!

In article <1149184049.192065.160910@i39g2000cwa.googlegroups.com>,
evilfeevil@gmail.com wrote:

> I have a worksheet with a column that counts using COUNTA, everything
> in a cell - text, numbers, all nonblank cells. But I don't want it to
> count the occurrence of any of this text: "NA", "N/A", or "DI" (I do
> want it to count all dates, the word "COMPLETED", and possibly other
> data in the future.). Right now the formula is only excluding
> occurrences of "NA" from the count:
>
> =(COUNTA(H11:H77)-COUNTIF(H11:H77,"NA"))
>
> How do I tell it to exclude counting either "NA", OR "N/A" OR "DI"? I
> tried putting a comma after "NA", then putting "N/A", etc, but it
> didn't work (neither did semicolons), like this:
>
> =(COUNTA(H11:H77)-COUNTIF(H11:H77,"NA","N/A","DI"))
>
> For now I've put in three separate COUNTIF statements, like this:
>
> =(COUNTA(I11:I77)-COUNTIF(I11:I77,"NA")-COUNTIF(I11:I77,"N/A")-COUNTIF(I11:I77
> ,"DI"))
>
> ...and it works, but it's so long, it looks sloppy and confusing and if
> I want to add more criteria in the future, I don't want to make this
> formula a mile long. Isn't there an easier way to do it? I guess my
> main question is, is there somewhere I can see a list of the many
> formula calculation operators in Excel, especially the criteria
> separator(s)? I found a list of the different operators and what they
> do in the Excel Help menu, but I still can't find the answer or an
> example of how to put my text exclusions together in the formula.
> Thanks.

[Jerry W. Lewis]

There is no syntax to support multiple conditions in COUNTIF. For more
complicated situations, SUMPRODUCT is often used. But for your situation,
you already have the simplest and most readable solution.

Jerry

"evilfeevil@gmail.com" wrote:

> I have a worksheet with a column that counts using COUNTA, everything
> in a cell - text, numbers, all nonblank cells. But I don't want it to
> count the occurrence of any of this text: "NA", "N/A", or "DI" (I do
> want it to count all dates, the word "COMPLETED", and possibly other
> data in the future.). Right now the formula is only excluding
> occurrences of "NA" from the count:
>
> =(COUNTA(H11:H77)-COUNTIF(H11:H77,"NA"))
>
> How do I tell it to exclude counting either "NA", OR "N/A" OR "DI"? I
> tried putting a comma after "NA", then putting "N/A", etc, but it
> didn't work (neither did semicolons), like this:
>
> =(COUNTA(H11:H77)-COUNTIF(H11:H77,"NA","N/A","DI"))
>
> For now I've put in three separate COUNTIF statements, like this:
>
> =(COUNTA(I11:I77)-COUNTIF(I11:I77,"NA")-COUNTIF(I11:I77,"N/A")-COUNTIF(I11:I77,"DI"))
>
> ....and it works, but it's so long, it looks sloppy and confusing and if
> I want to add more criteria in the future, I don't want to make this
> formula a mile long. Isn't there an easier way to do it? I guess my
> main question is, is there somewhere I can see a list of the many
> formula calculation operators in Excel, especially the criteria
> separator(s)? I found a list of the different operators and what they
> do in the Excel Help menu, but I still can't find the answer or an
> example of how to put my text exclusions together in the formula.
> Thanks.
>
>

[SteveG]

Similiar to Domenic's using the SUMPRODUCT instead,

=COUNTA(A1:A100)-SUMPRODUCT(--(A1:A100={"N/A","NA","DI",""""}))

SUMPRODUCT counts blank cells so you need to include the """" in the range to subtract.

HTH

Steve