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.

Name	Location	Status
Tomika Risch	QB3	P
Shakita Larocco	QB3	S
Barbera Auguste	QB4	S
Evangeline Corry	QB5	T
Carlee Empey	QB5	P
	A = EXCUSED ABSENCE			S = RST	Z = PENDING LOSS
	U = UNEXCUSED ABSENCE			T = TRAINING	P = Present
		PRE SENT	RST	NO SHOW	TRAINING
TOTAL ASSIGNED		2	2	0	1
QB3, QB4		1	2	0	0
QB5		1	0	0	1

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

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]

	F	G	H	I	J	K
3	Name	Location	Status
4	Tomika Risch	QB3	P
5	Shakita Larocco	QB3	S
6	Barbera Auguste	QB4	S
7	Evangeline Corry	QB5	T
8	Carlee Empey	QB5	p
9
10
11			PRE SENT	RST	NO SHOW	TRAINING
12	TOTAL ASSIGNED		2	2		1
13	QB3, QB4		1	2
14	QB5		1			1

<tbody>
</tbody>

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!