Need Help - Can't Wrap my Head Around SS Logic

I'm trying to build a metrics table that can be used to generate a graph on a dashboard.

I am a consultant and would like to track client hours from a master sheet that logs all clients, dates of services, and total hours.

Let's say I wanted to track the hours of utilization from a particular client from YTD, Last Month, Last Week, Weekly Average over the year, etc.

So if the Client Column is Client X, Add the hours in the hours column over the last month, week, year, etc.

I don't know why I'm struggling so much with this, but I can't seem to get it to work out right. Please help!

Find more posts tagged with

Sales

Manufacturing

Reports

Product Development

Project Management

Comments

LeaMurgue

Hello!

Have you tried using the VLOOKUP formula?

https://help.smartsheet.com/function/vlookup

DLABS Consulting

Just tried that out for this purpose and it doesn't return the value that I need. I will say that this will help me on some other sheets I'm working on, so thanks for that!

Here is a rough example of the sheet, keep in mind there are several additional columns in the sheet that are not relevant to this question.

What I'm trying to collect is, How many total hours were used by a particular client last month, this month, last week, this week, etc.

Example SS Table.PNG

David.O

Based on what I'm seeing in your sheet, this is the formula that I would use:

=sum(collect(Hours1:Hours6, [Client Name]1:[Client Name]6, @cell="Client 1", [Created Date]1:[Created Date]6, year(@cell)=year(today())))

This will give you the total hours recorded for Client 1 YTD. For last month's numbers, make the last criterion {month(@cell)=month(today())-1}. For last week it would be {weeknumber(@cell)=weeknumber(today())-1}.

For the client name, instead of putting the client name in the formula, you can point that at a cell that has the client that you are looking for.

Hope this helps.

DLABS Consulting

I think this is it, thanks!

There seems to be about a 1000 ways to get this data. I was going in a completely different direction and was way off.

DLABS Consulting

I have a new problem that perhaps you can help solve. For some reason, when I try to use the same formula for a different client, the only thing I am changing in the formula is the client name in the quotes and I'm getting a #INVALID DATA TYPE.

Not sure why, it makes absolutely not sense to me. Everything else is the same.

DLABS Consulting

I think I know why, I'm basing it on completed date and some of the fields have nothing in them, so I believe I need an IFERROR =0 statement. Where would this best fit in?

David.O

If you have only changed the client name and everything else in the formula is exactly the same, then the formula might be pointing at a cell with bad data. Check your 'date' and 'hours' columns and make sure they are all in the correct format.

You can try using iferror with your date criterion {iferror(year(@cell),0)=year(today())}. This will return a '0' if the date is in the wrong format. If you do that and don't get an error message than you know that something is up in the date column. Perhaps an empty cell or something else.

Anyways, give that a try and hopefully that'll do the trick.

David.O

you're right. See my previous comment.

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…