I have a sheet with employee time logs, as follows:
Name Day1 Day2 Subtotal of 'OFF' days this period Jess PRESENT OFF 1 Bob PRESENT PRESENT 0 Name Day3 Day4 Subtotal of 'OFF' days this period Jess PRESENT PRESENT 0 Bob OFF OFF 2
I need to create an automatic total of the number of days off each employee has had. How could I do this? =COUNTIF(1:100,"OFF")
would return the total number of off days, but is there a way to get this total only rows where the first cell contains "Jess"?
(In this example, "OFF" is "STRING" in my question, while "Jess" is "IFTEXT")
Best Answer
If you don't want to precalculate the subtotal of off days, you can use an array formula:
A:A="Jess"
returns a bunch ofTRUE
andFALSE
, in your sample, it would be{TRUE, FALSE, FALSE, TRUE, FALSE}
B:C="OFF"
returns another bunch ofTRUE
andFALSE
, in your sample, it would be{FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE}
(note semicolons denoting the different rows)And those two multiplied is evaluated as:
Only a
TRUE
multiplied by anotherTRUE
gives a result ofTRUE
. The*1
last converts everyTRUE
to1
.SUM
then adds all the1
together.