Countifs in Rows & Columns with Date Reference

[raymhuber]

Hi, I have a large workbook tracking the attendance of various employees. I need to count how many people have certain events, ie how many people were late in a month or how many people are ill.

I am getting an error whenever I use the following type of countifs, it has something to do with trying to use countifs in a whole table vs one row.

I put some sample data below (P = Present, S = Start, P = present, L = late). An example formula for determining how many people are late is Countifs(B2:F5,"L",B1:F1, ">=" & 1/1/18, B1:E1, "<" & 2/1/18) to count the number of times people were late in the month of January. This gives a #Value error, although if you only looked in one row, for instance B2:F2 it does not have an issue. Right now I am using an intermediary table to sum the number of each type per day, then sum each type by month, but I want to get it down to one formula. Does anyone know how you would go about doing this? Maybe some sort of array match formula?


[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]1/1/18[/TD]
[TD]1/2/18[/TD]
[TD]1/3/18[/TD]
[TD]1/4/18[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]A-Ill[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD]S[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]P[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 312"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[raymhuber]

In case anyone else has a similar question, I have figured out the answer and put it below.

Instead of using a Countifs statement, use a SUMPRODUCT. For this case, the syntax would be SUMPRODUCT((B2:F5 = "L") * (B1:F1 >= 1/1/18 ) * ( B1:E1 < 2/1/18)) where the dates are actual date code values. In my case, I have them referenced from another cell.

 

[Special-K99]

Try

=Countifs(B2:F5,"L",B1:F1, ">=" & DATEVALUE("1/1/18"), B1:E1, "<" & DATEVALUE("2/1/18"))

 

[raymhuber]

Hi Special-K99

The issue was not with the dates, whose were actual values, I only wrote them as text to make it more clear.

I belive the issue is a limitation of countifs since it is a 1 row by x column reference verse a x rows by x columns reference. I'm any case the sumproducts works great