Best Formula to use to COUNTIFS based on several criteria.


I'm trying to come up with a formula that will return a number based on several criteria. I have a separate sheet that I have a key of allotted days off for each area (Vacation, Personal, Summer Friday, and so on). On that separate sheet, I have an available days based on the Key, minus the formula that i'm trying to figure out

This is what I'm trying to do:


I technically want it to provide me the NUMBER OF DAYS from that cell, based on the following, the name of that person, if it's a VACATION DAY (Personal, summer friday, and so on), and the approval is "Approved".

Does that make sense?

Time off request sheet:

Key Available Dates Sheet:


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 03/06/20

    I think you would want to use a SUMIFS instead of COUNTIFS. COUNTIFS would count cells, SUMIFS would add up the values in cells. Utilize the "reference another sheet" functionality when you create your SUMIFS in order to reference the columns in the Time off Request sheet.

    =SUMIFS({Time Off Request - Days}, {Time Off Request - Name}, NAME@row, {Time Off Request - Vacation}, 1, {Time Off Request - Jay Approval}, "Approved")

    Something like the above but of course insert your own references, and you may need to reference the NAME column for the parent row.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!