Hey guys,
I have been looking for a way to count the number of cells in a column that do not contain the word "No." I used the countif formula to count the cells that do contain "No" but I need a formula to count cells that contain anything but the word no.
Let me know if this is possible!
Thanks again Excel Forum!
It will be the same as countif you've done before for cells that DO contain No.
But instead of "No", use "<>No"
That seems like a feasible solution except that I have selected a huge range of cells that I want to be updated as I enter in new information. I have 12 cells that have been counted as No and 383 that have been counted as everything but no haha. In reality it is 10 cells that do not contain No. So is there a way for it to only count text?Originally Posted by Jonmo1
ok, so there's a bunch of blanks in the range?
If you already have a countif that counts how many DO have no, then you can do
=COUNTA(range)-A1
Assuming A1 is the cell you already have your countif(range,"no") formula
Basically counts how many cells have any value, then subtracts the result of your original countif of no's.
Not sure why, but it is returning only 1.
Can you post a sample book?
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Another way is to have a helper column to determine what is in the cells and then count them.
ie
column A are your values
Column B =IF(A1>"",IFERROR(IF(SEARCH("No",A1,1)>0,0,1),1),"") this will see if the cell contains the word no or No or NO
Column C =IF(A1>"",IFERROR(IF(FIND("No",A1,1)>0,0),1),"") this will see if the cell contains the word No only - Case sensitive
copy these formulas down the columns.
then you can just do a sum of the columns to count how many values contain the word no depending on Case.
Regards
Sean
Please add to my reputation if you think i helped (click on the star below the post)
Mark threads as "Solved" if you have your answer (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [code]
Please supply a workbook containing example Data: It makes its easier to answer your problem & saves time!
Sample.xlsx
Here you are.
Misplaced ) in G6
=COUNTA(B4:B25-E6)
should be
=COUNTA(B4:B25)-E6
Ah, there it is! My mistake. Thanks for your help!
You're welcome.
if you change E6 to =COUNTIF(B4:B25, "No*")
then it will deal with the value No,completed
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks