# Nested COUNTIF formula

Options
edited 12/09/19

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?

Tags:

• Options

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)

• ✭✭✭✭✭✭
Options

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.

• Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Happy to help!

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!