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!

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…