Forum Discussion
IF Statement and adding a value based on date
I'd like to create a formula that works if the day in this calendar I made has an "L" and its only today or any day previously.
This is a rotation calendar, and if the day (or previous days) as an "L", it'll add 10 to a specific cell continuing through the end of the month, only working if its today or earlier. In this example, Employee A has an "L" on 10/5, and Id like if at the end of the month it added a 10 to the cell.
I've used this and it didnt work, but the logic is there.
=SUMPRODUCT(IF(AND(B1:AF1<=TODAY(),B8:AE8="L"),10,0))
1 Reply
And returns only a single TRUE/FALSE value, not an array. You can use this instead:
=SUMPRODUCT(IF((B1:AF1<=TODAY())*(B8:AF8="L"),10,0))
Alternatively:
=10*COUNTIFS($B$1:$AF$1,"<="&TODAY(), $B8:$AF8, "L")
Format the cell with the formula as General, then fill down.
