COUNTIFS(AND( help

[SFCChase]

[TABLE="class: grid, width: 750, align: center"]
<tbody>[TR]
[TD]UNIT

[/TD]
[TD]ATTENDANCE
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]UNIT
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28CSH

[/TD]
[TD]SCHEDULED
[/TD]
[TD]8
[/TD]
[TD]12
[/TD]
[TD]12
[/TD]
[TD]8
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD]28CSH
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ACTUAL
[/TD]
[TD]PRES
[/TD]
[TD]PRES
[/TD]
[TD]FTR
[/TD]
[TD]LV
[/TD]
[TD]PRES
[/TD]
[TD][/TD]
[TD]601 ASMC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28CSH
[/TD]
[TD]SCHEDLED
[/TD]
[TD]12
[/TD]
[TD]12
[/TD]
[TD]12
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ACTUAL
[/TD]
[TD]PRES
[/TD]
[TD]PRES
[/TD]
[TD]PRES
[/TD]
[TD]FTR
[/TD]
[TD]LV
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]601 ASMC
[/TD]
[TD]SCHEDULED
[/TD]
[TD]12
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]12
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ACTUAL
[/TD]
[TD]PRES
[/TD]
[TD]FTR
[/TD]
[TD]LV
[/TD]
[TD]PRES
[/TD]
[TD]PRES
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Looking for some help. I have a large spreadsheet that looks similar to the one above. I am trying to write a formula (in J2) that will count the number of times a 28CSH (Column A) person is PRESENT for work on the scheduled day (1,2,3,4,etc). Ideally the formula will scan A2:A7 for "28CSH" and count every time a person is PRES for a scheduled shift (so J2 would result 2, J3 would result 2, but J3 would only result 1). The formula will then be used to count for the other Units I have in the spreadsheet (like 601 ASMC). I was using COUNTIFS($A2:$A7,"28CSH",C2:C7,12)+COUNTIFS($A2:$A7,"28CSH",C2:C7,8) but it wasn't giving me actual attendance (only scheduled). Is there a way to add in some AND logic to count only those that are PRES?

 

[jasonb75]

Try this in J2, then drag right and down.

=COUNTIFS($A$2:$A$7,$I2,$B$3:$B$8,"ACTUAL",C$23:C$8,"PRES")

 

[jasonb75]

I just noticed a typo in the formula above, it should be

=COUNTIFS($A$2:$A$7,$I2,$B$3:$B$8,"ACTUAL",C$3:C$8,"PRES")

Note that the ranges used to count "actual" and "pres" start and finish one row below the type range.

If your real data is consistent with the pattern of your example, then you could also omit "actual" from the formula and just use

=COUNTIFS($A$2:$A$7,$I2,C$3:C$8,"PRES")