Personnel Report questions

[DarkBebi]

Hello everyone,

Excuse me if this is too hard or too simple. I absolutely love spreadsheets and I believe that a lot can be accomplished by automation. I am trying to automate numbers of personnel that are either in today, sick, or just didn't show up.

I have the list of people and next to that where they are located and then next to that I have their status. What I want to do is look up in 2 rows and if they both have the characters I want, it produces a result. I apologize I do not know how to explain this very well.

Below is a example of that I am talking about, names are fake of course but I want to automate the bottom part.


[TABLE="width: 539"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tomika Risch[/TD]
[TD]QB3[/TD]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shakita Larocco[/TD]
[TD]QB3[/TD]
[TD]S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Barbera Auguste[/TD]
[TD]QB4[/TD]
[TD]S[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Evangeline Corry[/TD]
[TD]QB5[/TD]
[TD]T[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Carlee Empey[/TD]
[TD]QB5[/TD]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]A = EXCUSED ABSENCE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]S = RST[/TD]
[TD]Z = PENDING LOSS[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]U = UNEXCUSED ABSENCE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]T = TRAINING[/TD]
[TD]P = Present[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]PRE SENT [/TD]
[TD]RST[/TD]
[TD]NO SHOW[/TD]
[TD]TRAINING[/TD]
[/TR]
[TR]
[TD]TOTAL ASSIGNED[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]QB3, QB4[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]QB5[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]


Could anyone please help me with this? At the least point me what formula to use. I have came up with a few but they are not real formulas like
"=COUNTIF(F4:F83,"QB3" & I4:I83, "S")+COUNTIF(F4:F83,"QB4" & I4:I83, "S")". Please help.

 

[Scott T]

[TABLE="class: grid, width: 433"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name
[/TD]
[TD]Location
[/TD]
[TD]Status
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tomika Risch
[/TD]
[TD]QB3
[/TD]
[TD]P
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Shakita Larocco
[/TD]
[TD]QB3
[/TD]
[TD]S
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Barbera Auguste
[/TD]
[TD]QB4
[/TD]
[TD]S
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Evangeline Corry
[/TD]
[TD]QB5
[/TD]
[TD]T
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Carlee Empey
[/TD]
[TD]QB5
[/TD]
[TD]p
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]PRE SENT
[/TD]
[TD]RST
[/TD]
[TD]NO SHOW
[/TD]
[TD]TRAINING
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]TOTAL ASSIGNED
[/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]QB3, QB4
[/TD]
[TD][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]QB5
[/TD]
[TD][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]


In H12

=COUNTIF($H$4:$H$8,"P")

In H13

=COUNTIFS($G$4:$G$8,"QB3",$H$4:$H$8,"P")+COUNTIFS($G$4:$G$8,"QB4",$H$4:$H$8,"P")

In H14

=COUNTIFS($G$4:$G$8,$F14,$H$4:$H$8,"P")

copy the formals across just replace the P with S for RST and so forth

I assume that No Show is both U and A if so you can use

=COUNTIF($H$4:$H$8,"U")+COUNTIF($H$4:$H$8,"A")

 

[DarkBebi]

Thank you so much, very fast reply and very clean code, I understood it right away. I did not know about COUNTIFS, only COUNTIF. Very much appreciated!