Dcounta for counting staff grades

[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

 

[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

 

[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)

 

[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

 

[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

 

[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

 

[Pete_UK]

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

Pete

 

[John in Wembley]

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

Pete

thanks Pete, Dcounta requires qutie a few querries

cheers from wet wembley

 

[Pete_UK]

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

Pete