COUNTIFS(AND( help

sgtchase

Board Regular
Joined
Jun 25, 2013
Messages
63
UNIT

ATTENDANCE
1
2
3
4
5
UNIT
1
2
3
4
5
28CSH

SCHEDULED
8
12
12
8
12
28CSH
ACTUAL
PRES
PRES
FTR
LV
PRES
601 ASMC
28CSH
SCHEDLED
12
12
12
8
8
ACTUAL
PRES
PRES
PRES
FTR
LV
601 ASMC
SCHEDULED
12
8
8
12
12
ACTUAL
PRES
FTR
LV
PRES
PRES

<tbody>
</tbody>
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?
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,933
Office Version
2019
Platform
Windows
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")
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,933
Office Version
2019
Platform
Windows
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")
 

Forum statistics

Threads
1,078,503
Messages
5,340,759
Members
399,393
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top