Forum Discussion
Giving a range of information in cells number values
I am making a schedule for my team that requires multiple parts. First I would like to give the shifts a value of 1 and the OFF days a value of 0. Then I would need a formula that when those are inputted into a cell the math is done at the top of each week. But if there's a way to make that also go along with the color as I have 1 team at a building and the rest at another buidling.
2 Replies
formulas can't use and can't create color but what I THINK you want it to do a type of countif. Basically count the number of cells that have "A.C." and count cells that have "PAC" in them (note the "OFF" you wanted to be 0 anyhow so we don't need them unless you have "OFF A.C." or the like in which case those would need to be filtered out also).
But since you can't just do an easy direct comparison, COUNTIFS probably wouldn't work well, so instead maybe try this in B5:
=IFERROR(ROWS(FILTER(B$9:B$20, ISNUMBER(SEARCH("A.C.", B$9:B$20)))),0)and in B6:
=IFERROR(ROWS(FILTER(B$9:B$20, ISNUMBER(SEARCH("PAC", B$9:B$20)))),0)You may consider conditional formatting and SUMIF:
Example:
=SUMIF(B2:H2, 1)
