Hi
I have a roster at work and use DCOUNTA to count how may qualified
nurses I have on each shift. I need to count the nurses with grade
E,F,G present on that shift
Am I correct in using Dcounta, is there any other option.
I just ask as I need to do quite a bit of work to create all the
querries for each day of the month

cheers
John

Assuming your grade column is G, then you could do it like this:

=COUNTIF(G:G,"E")+COUNTIF(G:G,"F")+COUNTIF(G:G,"G" )

if you have one sheet per shift. If all the data is in one sheet, with
another column to denote the shift, then a SUMPRODUCT formula could
get you the count per shift - post back with further details
describing what you have and what you want to do if you require more
assistance.

Hope this helps.

Pete

On Nov 18, 7:17 pm, John in Wembley wrote:
Hi
I have a roster at work and use DCOUNTA to count how may qualified
nurses I have on each shift. I need to count the nurses with grade
E,F,G present on that shift
Am I correct in using Dcounta, is there any other option.
I just ask as I need to do quite a bit of work to create all the
querries for each day of the month

cheers
John

=SUMPRODUCT(COUNTIF(B:B,{"E","F","G"}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"John in Wembley" wrote in message
...

Hi
I have a roster at work and use DCOUNTA to count how may qualified
nurses I have on each shift. I need to count the nurses with grade
E,F,G present on that shift
Am I correct in using Dcounta, is there any other option.
I just ask as I need to do quite a bit of work to create all the
querries for each day of the month

cheers
John

On Sun, 18 Nov 2007 12:28:30 -0800 (PST), Pete_UK
wrote:

describing what you have and what you want to do if you require more
assistance.

staff grade mon tue wed
john A s s off
steve B off s s
anne C s s s

qualified one two one
(a,b grades)
total staff two three two


thanks to all

You seem to have changed your grades !

I would suggest that you put your totals in the top two rows, rather
than at the bottom, so that you don't have to keep scrolling down and
your table can grow without messing up the formulae. So, insert two
rows at the top, so that your data now starts on row 4. In C1 (above
Monday) you can enter this formula:

=SUMPRODUCT((($B$4:$B$100="A")+($B$4:$B$100="B"))* (C4:C100="s"))

to give the number qualified who are on shift (="s"), and this one in
C2:

=COUNTIF(C4:C100,"s")

which will give the total staff on shift (excluding any "off""s).

Copy these two formulae across into the other columns to cover other
days. I've assumed you may have data up to row 100 - change this
reference if you have more.

Hope this helps.

Pete


On Nov 19, 6:50 pm, John in Wembley wrote:
On Sun, 18 Nov 2007 12:28:30 -0800 (PST), Pete_UK

wrote:
describing what you have and what you want to do if you require more
assistance.

staff grade mon tue wed
john A s s off
steve B off s s
anne C s s s

qualified one two one
(a,b grades)
total staff two three two

thanks to all

On Mon, 19 Nov 2007 18:50:18 GMT, John in Wembley
wrote:

On Sun, 18 Nov 2007 12:28:30 -0800 (PST), Pete_UK
wrote:

describing what you have and what you want to do if you require more
assistance.

staff grade mon tue wed
john A s s off
steve B off s s
anne C s s s

qualified one two one
(a,b grades)
total staff two three two
I need to count how many qualified staff on each shift...
eg Monday has one on shift., tue, two, wed, one

That is what my formulae do. Did you try them?

Pete

On Nov 19, 7:35 pm, John in Wembley wrote:
On Mon, 19 Nov 2007 18:50:18 GMT, John in Wembley

wrote:
On Sun, 18 Nov 2007 12:28:30 -0800 (PST), Pete_UK
wrote:

describing what you have and what you want to do if you require more
assistance.

staff grade mon tue wed
john A s s off
steve B off s s
anne C s s s

qualified one two one
(a,b grades)
total staff two three two

I need to count how many qualified staff on each shift...
eg Monday has one on shift., tue, two, wed, one

On Mon, 19 Nov 2007 11:37:11 -0800 (PST), Pete_UK
wrote:

That is what my formulae do. Did you try them?

Pete

thanks Pete, Dcounta requires qutie a few querries

cheers from wet wembley

Thanks for the feedback, John - it's dry here in Warrington, for a
change.

Pete

On Nov 19, 7:43 pm, John in Wembley wrote:
On Mon, 19 Nov 2007 11:37:11 -0800 (PST), Pete_UK

wrote:
That is what my formulae do. Did you try them?

Pete

thanks Pete, Dcounta requires qutie a few querries

cheers from wet wembley