Prev: Workbook causes calculation errors
Next: Countif Question
From: Rafa on 4 Jun 2010 11:29
Im working on a colum that is a medical schedule for Doctor who will be
working that day so that I know how many exam rooms will be needed - right
now Im working with: =COUNTIF(F14:F43,"*") which tells me how many cells are
not blank. But I would like to have countif not count cells in that same
colum who have values like: OFF, ADMIN, OFF SITE and etc. Am I using the
correct function? PLEASE HELP!
From: Roger Govier on 4 Jun 2010 11:36
Hi Rafa

Countif is a very efficient function
=COUNTIF(F14:F43,"OFF*")
will deal with both Off and Off Site
=COUNTIF(F14:F43,"Admin")

Deduct these values from your other value to get the net figure.
--

Regards
Roger Govier

"Rafa" <Rafa(a)discussions.microsoft.com> wrote in message
news:88A5D359-DC14-463E-A8A4-1EFCF9470476(a)microsoft.com...
> Im working on a colum that is a medical schedule for Doctor who will be
> working that day so that I know how many exam rooms will be needed - right
> now Im working with: =COUNTIF(F14:F43,"*") which tells me how many cells
> are
> not blank. But I would like to have countif not count cells in that same
> colum who have values like: OFF, ADMIN, OFF SITE and etc. Am I using the
> correct function? PLEASE HELP!
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5172 (20100604) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 5172 (20100604) __________

The message was checked by ESET Smart Security.

http://www.eset.com



From: Jackpot on 4 Jun 2010 11:40
If you are looking for exact words in the cells then try
=SUM(COUNTIF(F14:F43,{"OFF","Admin","Off site"})

OR incase you are looking for text string within the entries then try the
below..You can ignore Off site since "Off" is already mentioned in the search
list

=SUM(COUNTIF(F14:F43,"*" & {"OFF","Admin"} & "*"))


"Rafa" wrote:

> Im working on a colum that is a medical schedule for Doctor who will be
> working that day so that I know how many exam rooms will be needed - right
> now Im working with: =COUNTIF(F14:F43,"*") which tells me how many cells are
> not blank. But I would like to have countif not count cells in that same
> colum who have values like: OFF, ADMIN, OFF SITE and etc. Am I using the
> correct function? PLEASE HELP!
 | 
Pages: 1
Prev: Workbook causes calculation errors
Next: Countif Question