Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |