COUNTIF formula multiple text exclusions
 

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.

COUNTIF formula multiple text exclusions
 

You could use this.

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

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=547599

COUNTIF formula multiple text exclusions
 

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 .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.

COUNTIF formula multiple text exclusions
 

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

" 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.

COUNTIF formula multiple text exclusions
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=547599