Nested COUNTIF formula

Hi!

I am hoping someone can help me solve this formula. What I am trying to do is keep a running total of hours of vacation used. For example, If Joe wants to take an 8 hour vacation he will go to the dates and type in "V8" (Vacation, 8 hours) or 4 hours would be "V4".ETC. When this is entered I want those hours to be subtracted from the column that shows the total amount of vacation hours allowed. In excel, my COUNTIF formula works great for this. I would use a formula that looked something like this in the hours used colum.  As this is calculated, it will be subtracted from their total hours column to determine their remaining balance.

 

=COUNTIF(([6-31]4:[12-31]4,"V8)*8)+(COUNTIF([6-31]4:[12-31]4,"V4")*4)

 

When used in smartsheet I get the #UNPARSEABLE error. How can I achieve this in smartsheet? 

 

Thanks for your help!

Tags:

Comments

  • Looks like it's unparsable because you're missing a " after "V8 in your first countif statement:

    =COUNTIF(([6-31]4:[12-31]4,"V8)*8)+(COUNTIF([6-31]4:[12-31]4,"V4")*4)

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Amanda,

    There are several errors in the formula.

    Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Good morning. This is not the formula I'm using. Just a quick type up to describe what i'm looking to do. This is the excel formula I am trying to recreate. This runs smoothly through excel but doesn't appear to work the same through smartsheet

    =COUNTIF($I9:$GI9,"v1")+(COUNTIF($I9:$GI9,"v2")*2)+(COUNTIF($I9:$GI9,"v3")*3)+(COUNTIF($I9:$GI9,"v4")*4)+(COUNTIF($I9:$GI9,"v5")*5)... Etc. 

     

    This formula will be in the "Used" column to keep a running total of Vacation/PTO hours. "V8"= 8 hours of vacation. "V2" is two hours. Ex. John has 4 "V8" currently scheduled so it should be counting every time "V_" is entered multiplied by the amount of hours it says. Hours used should calculate at 32 in the john example. If john had a  "V2", 3 "V4", and a V1 the formula should calculate to be 15.Hopefully this makes sense. It's difficult to try to type out what i'm trying to accomplish. 

     

    Capture.PNG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would actually suggest creating another row right below. You can eventually indent the row to "hide" it for a cleaner look.

     

    In that row you would basically use 

    =VALUE(SUBSTITUTE([Current Column]4, "V", ""))

    to remove the V and convert the number back into a numerical value.

    You can then sum up that row and put the result in the visible row of the Used Column.

  • This seems like a good way to go about this. Would I have to create that row under every single name? 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Unfortunately yes. But if you have multiple types of leave (V, S, H, etc...), You can use a SUMIFS to only pull the numbers that came from a specific type base don the letter in the main row.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!