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

=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

=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

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

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