Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning Multiple Text or Address Locations | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |