Excel's absence schedule template: how do I omit a "key" from being counted in the table?

New Contributor

Hello, 

I am developing a schedule for staff at work. In the template (found here: https://templates.office.com/en-us/employee-absence-schedule-tm03987167) I want to document days that our staff reports to the office, but I don't want those days counted in the total column. What do I do? 

4 Replies

@Damien1020  The easiest way is to subtract the that number using -COUNTIF(range, "in office code") so in the attached example I added "O" as the key for in office and changed the total count formula to be:

=COUNTA(January!$C7:$AG7)-COUNTIF(January[@[1]:[31]],"O")
Wonderful! I will be using =COUNTA(January!$C7:$AG7)-SUM(COUNTIF(C7:AG7,{"O"}). Thank you!
Do you have a formula for the subtotal? I would like a formula that excludes "O." Much appreciated!

@Damien1020 you can do the same thing:

=SUBTOTAL(103,E$7:E$11)-COUNTIF(E$7:E$11,"O")

I updated Jan tab in the attached.