Nested COUNTIF formula

Amandadg1223

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!

mwilliams1216

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å

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

Amandadg1223

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. 

 

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.

Amandadg1223

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

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å

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée