Dcounta for counting staff grades


J

John in Wembley

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
 
Ad

Advertisements

P

Pete_UK

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
 
B

Bob Phillips

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

--
---
HTH

Bob

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

John in Wembley

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
 
P

Pete_UK

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
 
J

John in Wembley

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
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top