New Contributor

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

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

# Re: Excel's absence schedule template: how do I omit a "key" from being counted in the tab

@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")``

# Re: Excel's absence schedule template: how do I omit a "key" from being counted in the tab

Wonderful! I will be using =COUNTA(January!\$C7:\$AG7)-SUM(COUNTIF(C7:AG7,{"O"}). Thank you!

# Re: Excel's absence schedule template: how do I omit a "key" from being counted in the tab

Do you have a formula for the subtotal? I would like a formula that excludes "O." Much appreciated!

# Re: Excel's absence schedule template: how do I omit a "key" from being counted in the tab

@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.