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(([631]4:[1231]4,"V8)*8)+(COUNTIF([631]4:[1231]4,"V4")*4)
When used in smartsheet I get the #UNPARSEABLE error. How can I achieve this in smartsheet?
Thanks for your help!
Comments

Looks like it's unparsable because you're missing a " after "V8 in your first countif statement:
=COUNTIF(([631]4:[1231]4,"V8)*8)+(COUNTIF([631]4:[1231]4,"V4")*4)

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.

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?

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.

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
Categories
Check out the Formula Handbook template!