Count number of occurrences based on Criteria

Question

I have a simple dataframe with 4 columns of date, name, late in, and leave early. I am trying to count the number of late/early occurrences for a particular person within the past 30 days.

I've tried using count and countif with no success.

Date     Name    Late    Leave
08/01    John    9:05    
         Sue    10:01
08/02    Bob             4:51    
08/03    Sue     9:16
         John            3:10
08/04
08/05    Bob     9:19
08/06    Sue             3:30

I have tried =countifs(B2:B8,B2,C2:D8,"<>") but receive a value error. I have also attempted using an array formula such as {=count(if(B2:B8=B2,C2:D8))} but I'm not getting the results I would like. I haven't even tried incorporating the 30 days back as I can't even get it to function properly in its current state.

What I would like to calculate for example is:

John had a total of 2 late/leave early in the month of August
Sue had a total of 3 late/leave early in the month of August
etc.

Answer 1

You need to have the dates filled in each cell for the greatest accuracy.

In the formulas below, replace $G$1 with the cell containing the target date or Today().

To get the number of occurrences in the 30 days prior to the target date, as stated in the question, use this formula:

=COUNTIFS(
$B$2:$B$9,$F4,$C$2:$C$9,"<>""",$A$2:$A$9,">="&$G$1-30,$A$2:$A$9,"<="&$G$1)+
COUNTIFS(
$B$2:$B$9,$F4,$C$2:$C$9,"<>""",$D$2:$D$9,">="&$G$1-30,$A$2:$A$9,"<="&$G$1)

However, if you are looking for the number of occurrences in the month of the target date, use this formula:

=COUNTIFS(
$B$2:$B$9,$F4,$C$2:$C$9,">0",$A$2:$A$9,">="&EOMONTH($G$1,-1)+1,$A$2:$A$9,"<"&EOMONTH($G$1,0)+1)+
COUNTIFS(
$B$2:$B$9,$F4,$D$2:$D$9,">0",$A$2:$A$9,">="&EOMONTH($G$1,-1)+1,$A$2:$A$9,"<"&EOMONTH($G$1,0)+1)

Answer 2

COUNTIFS require that the ranges be the same size. You will need to do two and add them together:

=COUNTIFS(B:B,F2,C:C,"<>")+COUNTIFS(B:B,F2,D:D,"<>")