Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count if not blank...
I have Office 2003. I am trying to count all cells in a row that are not
blank. The problem is coming in because this all has to be somewhat automated, meaning that users should be able to add new information, and it should automatically calculate the new info without having to update the formula. I was able to do so, but the formula is long, complicated, and ugly. There must be an easier way to do it, so please let me know if anybody knows. The problem is count only counts cells that have numbers, and the rows involved in my scenario have text. I thought count if might help, but there are three possible entries (Yes, no, or n/a) and I didn't know how to enter a criteria that would look for at least one of the three when counting. Here is the formula I came up with that worked: =(COUNTBLANK('Week of Oct 9 ''05'!E:E)+COUNTIF('Week of Oct 9 ''05'!E:E,"Yes")+COUNTIF('Week of Oct 9 ''05'!E:E,"No")+COUNTIF('Week of Oct 9 ''05'!E:E,"n/a"))-(COUNTBLANK('Week of Oct 9 ''05'!E:E)) What this does is adds everything in the range, those that are blank, those that have "yes", those that have "no", and those that have "n/a." Then, it will subtract that number from the number that are blank to get the number of entries, or the number of cells that are not blank. Is there any easier way to achieve the same result? -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy ~~~~~~ | | |c--OD | _) | | |-. | / `-# /A / /_|..`#.J/ ||LJ `m'' ptaylor |
#2
|
|||
|
|||
=256-countif(1:1,"=")
count the blanks instead and subtract from row total -- Gary''s Student "Paul (ESI)" wrote: I have Office 2003. I am trying to count all cells in a row that are not blank. The problem is coming in because this all has to be somewhat automated, meaning that users should be able to add new information, and it should automatically calculate the new info without having to update the formula. I was able to do so, but the formula is long, complicated, and ugly. There must be an easier way to do it, so please let me know if anybody knows. The problem is count only counts cells that have numbers, and the rows involved in my scenario have text. I thought count if might help, but there are three possible entries (Yes, no, or n/a) and I didn't know how to enter a criteria that would look for at least one of the three when counting. Here is the formula I came up with that worked: =(COUNTBLANK('Week of Oct 9 ''05'!E:E)+COUNTIF('Week of Oct 9 ''05'!E:E,"Yes")+COUNTIF('Week of Oct 9 ''05'!E:E,"No")+COUNTIF('Week of Oct 9 ''05'!E:E,"n/a"))-(COUNTBLANK('Week of Oct 9 ''05'!E:E)) What this does is adds everything in the range, those that are blank, those that have "yes", those that have "no", and those that have "n/a." Then, it will subtract that number from the number that are blank to get the number of entries, or the number of cells that are not blank. Is there any easier way to achieve the same result? -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy ~~~~~~ | | |c--OD | _) | | |-. | / `-# /A / /_|..`#.J/ ||LJ `m'' ptaylor |
#3
|
|||
|
|||
=COUNTA(E:E)
-- HTH Bob Phillips "Paul (ESI)" wrote in message ... I have Office 2003. I am trying to count all cells in a row that are not blank. The problem is coming in because this all has to be somewhat automated, meaning that users should be able to add new information, and it should automatically calculate the new info without having to update the formula. I was able to do so, but the formula is long, complicated, and ugly. There must be an easier way to do it, so please let me know if anybody knows. The problem is count only counts cells that have numbers, and the rows involved in my scenario have text. I thought count if might help, but there are three possible entries (Yes, no, or n/a) and I didn't know how to enter a criteria that would look for at least one of the three when counting. Here is the formula I came up with that worked: =(COUNTBLANK('Week of Oct 9 ''05'!E:E)+COUNTIF('Week of Oct 9 ''05'!E:E,"Yes")+COUNTIF('Week of Oct 9 ''05'!E:E,"No")+COUNTIF('Week of Oct 9 ''05'!E:E,"n/a"))-(COUNTBLANK('Week of Oct 9 ''05'!E:E)) What this does is adds everything in the range, those that are blank, those that have "yes", those that have "no", and those that have "n/a." Then, it will subtract that number from the number that are blank to get the number of entries, or the number of cells that are not blank. Is there any easier way to achieve the same result? -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy ~~~~~~ | | |c--OD | _) | | |-. | / `-# /A / /_|..`#.J/ ||LJ `m'' ptaylor |
#4
|
|||
|
|||
Thanks to you both for your help. Unfortunately, neither worked, and I think
I know why. I forgot to mention one thing that I suppose was important to the issue. Row 1 has column headings, and therefore should not be counted. Using the suggestions here wound up counting that too. I think I may just have to go with my ugly formula considering that. -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy ~~~~~~ | | |c--OD | _) | | |-. | / `-# /A / /_|..`#.J/ ||LJ `m'' ptaylor "Bob Phillips" wrote: =COUNTA(E:E) -- HTH Bob Phillips "Paul (ESI)" wrote in message ... I have Office 2003. I am trying to count all cells in a row that are not blank. The problem is coming in because this all has to be somewhat automated, meaning that users should be able to add new information, and it should automatically calculate the new info without having to update the formula. I was able to do so, but the formula is long, complicated, and ugly. There must be an easier way to do it, so please let me know if anybody knows. The problem is count only counts cells that have numbers, and the rows involved in my scenario have text. I thought count if might help, but there are three possible entries (Yes, no, or n/a) and I didn't know how to enter a criteria that would look for at least one of the three when counting. Here is the formula I came up with that worked: =(COUNTBLANK('Week of Oct 9 ''05'!E:E)+COUNTIF('Week of Oct 9 ''05'!E:E,"Yes")+COUNTIF('Week of Oct 9 ''05'!E:E,"No")+COUNTIF('Week of Oct 9 ''05'!E:E,"n/a"))-(COUNTBLANK('Week of Oct 9 ''05'!E:E)) What this does is adds everything in the range, those that are blank, those that have "yes", those that have "no", and those that have "n/a." Then, it will subtract that number from the number that are blank to get the number of entries, or the number of cells that are not blank. Is there any easier way to achieve the same result? -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy ~~~~~~ | | |c--OD | _) | | |-. | / `-# /A / /_|..`#.J/ ||LJ `m'' ptaylor |
#5
|
|||
|
|||
If that header row always has something in it, just subtract 1:
=counta(e:e)-1 Paul (ESI) wrote: Thanks to you both for your help. Unfortunately, neither worked, and I think I know why. I forgot to mention one thing that I suppose was important to the issue. Row 1 has column headings, and therefore should not be counted. Using the suggestions here wound up counting that too. I think I may just have to go with my ugly formula considering that. -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy ~~~~~~ | | |c--OD | _) | | |-. | / `-# /A / /_|..`#.J/ ||LJ `m'' ptaylor "Bob Phillips" wrote: =COUNTA(E:E) -- HTH Bob Phillips "Paul (ESI)" wrote in message ... I have Office 2003. I am trying to count all cells in a row that are not blank. The problem is coming in because this all has to be somewhat automated, meaning that users should be able to add new information, and it should automatically calculate the new info without having to update the formula. I was able to do so, but the formula is long, complicated, and ugly. There must be an easier way to do it, so please let me know if anybody knows. The problem is count only counts cells that have numbers, and the rows involved in my scenario have text. I thought count if might help, but there are three possible entries (Yes, no, or n/a) and I didn't know how to enter a criteria that would look for at least one of the three when counting. Here is the formula I came up with that worked: =(COUNTBLANK('Week of Oct 9 ''05'!E:E)+COUNTIF('Week of Oct 9 ''05'!E:E,"Yes")+COUNTIF('Week of Oct 9 ''05'!E:E,"No")+COUNTIF('Week of Oct 9 ''05'!E:E,"n/a"))-(COUNTBLANK('Week of Oct 9 ''05'!E:E)) What this does is adds everything in the range, those that are blank, those that have "yes", those that have "no", and those that have "n/a." Then, it will subtract that number from the number that are blank to get the number of entries, or the number of cells that are not blank. Is there any easier way to achieve the same result? -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy ~~~~~~ | | |c--OD | _) | | |-. | / `-# /A / /_|..`#.J/ ||LJ `m'' ptaylor -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count blank cells since last entry | Excel Worksheet Functions | |||
formula to count cells not blanK | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
count non blank cells which meet criteria in another column | Excel Worksheet Functions | |||
COUNT ONLY CELLS THAT AREN'T BLANK | Excel Worksheet Functions |