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

Mr
hi would like to create a simple patient tracking system for our small clinic where i can record the time & duration a patient appeared in our establishment. Using a central sheet where it records all the data to be accessible by the admin. There will also be Four (4) forms to be accessible by the three department. Concept…
Able to swtich views in a published version?
I have published a calendar in the past and end users could switch from calendar view to grid view. Has that option been taken away or do I need to re-save it in a new format? I am missing what I circled in the blue.
Forms
On the App - "In Smartsheet forms, is there a way to capture or upload an image directly into a specific column cell (per row) without using the Attachments field? I need each section of the form to have its own dedicated image, not just general attachments for the row."