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?
<tbody>
</tbody>
<tbody>
</tbody>
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?
Name | 1/1/18 | 1/2/18 | 1/3/18 | 1/4/18 |
Bob | P | P | P | A-Ill |
Fred | S | P | P | |
George | P | P | P | P |
Alan | P | L | P | L |
<tbody>
</tbody>
<tbody>
</tbody>
Last edited: