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

DLABS Consulting

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!

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.

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.