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
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!