COUNTIF - everything excluding a string

I'm using an Excel document to keep track of the cases I handle in my
department. I have several columns, B through L, with each cases information.
Once a case is determine to be resolved I fill the cells in row F (everything
7 and down) with *** CLOSED ***. I already have a basic function that goes
like this:

=COUNTIF(F:F,"*** CLOSED ***")

Pretty basic huh? Well that tells me how many cases I've closed in a pretty
forward way. Now the hard part is counting everything that doesn't have that
string in it, resulting in the cases that are still open. I would then have
another formula that figures the total of the two to give me my grand total
number of cases that I have handled. So what I'm asking is for a formula that
will tell me how many cases I have open. Again F7:F100.

For the sake of argument, I've got 13 worksheets, one for every month, and a
summary which I will use to analyze the cases as soon as I learn the
functions I'm looking for. As it is now, B2 on each worksheet has my close
count because that was the easy one. B1 is where I want the open cases
formula to go.

Thanks in advance for any help.

=COUNTA(F7:F100)-COUNTIF(F7:F100,"*** CLOSED ***")


--
Regards,
Dave


"sans" wrote:

> I'm using an Excel document to keep track of the cases I handle in my
> department. I have several columns, B through L, with each cases information.
> Once a case is determine to be resolved I fill the cells in row F (everything
> 7 and down) with *** CLOSED ***. I already have a basic function that goes
> like this:
>
> =COUNTIF(F:F,"*** CLOSED ***")
>
> Pretty basic huh? Well that tells me how many cases I've closed in a pretty
> forward way. Now the hard part is counting everything that doesn't have that
> string in it, resulting in the cases that are still open. I would then have
> another formula that figures the total of the two to give me my grand total
> number of cases that I have handled. So what I'm asking is for a formula that
> will tell me how many cases I have open. Again F7:F100.
>
> For the sake of argument, I've got 13 worksheets, one for every month, and a
> summary which I will use to analyze the cases as soon as I learn the
> functions I'm looking for. As it is now, B2 on each worksheet has my close
> count because that was the easy one. B1 is where I want the open cases
> formula to go.
>
> Thanks in advance for any help.

Try,
=COUNTIF(F:F,"<>"&"*** CLOSED ***")
Regards,
Alan.

"sans" <[email protected]> wrote in message
news:[email protected]...
> I'm using an Excel document to keep track of the cases I handle in my
> department. I have several columns, B through L, with each cases
> information.
> Once a case is determine to be resolved I fill the cells in row F
> (everything
> 7 and down) with *** CLOSED ***. I already have a basic function that goes
> like this:
>
> =COUNTIF(F:F,"*** CLOSED ***")
>
> Pretty basic huh? Well that tells me how many cases I've closed in a
> pretty
> forward way. Now the hard part is counting everything that doesn't have
> that
> string in it, resulting in the cases that are still open. I would then
> have
> another formula that figures the total of the two to give me my grand
> total
> number of cases that I have handled. So what I'm asking is for a formula
> that
> will tell me how many cases I have open. Again F7:F100.
>
> For the sake of argument, I've got 13 worksheets, one for every month, and
> a
> summary which I will use to analyze the cases as soon as I learn the
> functions I'm looking for. As it is now, B2 on each worksheet has my close
> count because that was the easy one. B1 is where I want the open cases
> formula to go.
>
> Thanks in advance for any help.

Sorry,
=COUNTIF(F7:F100,"<>"&"*** CLOSED ***")
Alan.
"Alan" <[email protected]> wrote in message
news:[email protected]...
> Try,
> =COUNTIF(F:F,"<>"&"*** CLOSED ***")
> Regards,
> Alan.
>
> "sans" <[email protected]> wrote in message
> news:[email protected]...
>> I'm using an Excel document to keep track of the cases I handle in my
>> department. I have several columns, B through L, with each cases
>> information.
>> Once a case is determine to be resolved I fill the cells in row F
>> (everything
>> 7 and down) with *** CLOSED ***. I already have a basic function that
>> goes
>> like this:
>>
>> =COUNTIF(F:F,"*** CLOSED ***")
>>
>> Pretty basic huh? Well that tells me how many cases I've closed in a
>> pretty
>> forward way. Now the hard part is counting everything that doesn't have
>> that
>> string in it, resulting in the cases that are still open. I would then
>> have
>> another formula that figures the total of the two to give me my grand
>> total
>> number of cases that I have handled. So what I'm asking is for a formula
>> that
>> will tell me how many cases I have open. Again F7:F100.
>>
>> For the sake of argument, I've got 13 worksheets, one for every month,
>> and a
>> summary which I will use to analyze the cases as soon as I learn the
>> functions I'm looking for. As it is now, B2 on each worksheet has my
>> close
>> count because that was the easy one. B1 is where I want the open cases
>> formula to go.
>>
>> Thanks in advance for any help.
>
>