What formula will pull client specific data from one sheet?

Hello!

I think what you might be looking for is adding a "filter" of sorts to the COUNTIF formulas. One way to do that is in the COUNTIFS, include a criteria that will only count tickets where Client Name = "Synergytest" for example.

So Last year for just Syngergytest would be:

=COUNTIFS({Created Date Range}, IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()) - 1, {Client Name}, "Synergytest")

What I have done before for displaying metrics like this on dashboards is create a metrics sheet based on a list of all groups, here a list of all Client Names. Then you can find individual client metrics with column formulas like the one above that count tickets in the last year, etc. for that client. Then the Last Year column formula could be:

=COUNTIFS({Created Date Range}, IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()) - 1, {Client Name}, [Client Name]) →where [Client Name] is in the metric sheet and matches data in the outside sheet.

One more suggestion/note you can consider:

If you wanted to use only one dashboard for all clients to view, you could use a current user filter report from the metric sheet. You would have to have the emails/contacts of anyone who would be viewing the dashboard on the metric sheet (ex. one row for Synergytest has column formulas for metrics and a column containing the contacts that will view that data). Then create a report from the metric sheet with a current user filter- filtering on the contact column. This report could power dashboard widgets and charts and would show only the data for whoever is currently viewing the dashboard.

@Malcolm Jackson I would suggest starting with simplifying your formula. That will make it much easier to determine where to put your additional range/criteria set.

To simplify, we can pull the week number from 7 days ago. This will be much easier to manage than trying to run two separate COUNTIFS based on going into the previous year and take care of any issues where there may be a week number of 53.

=COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY(-7)))

The above does exactly what your longer IF/COUNTIFS/COUNTIFS formula will do but is much easier to maintain and update.

Now that you only have a single COUNTIFS, adding the new range/criteria set for the client would be the same as your other COUNTIFS.

@Malcolm Jackson It wasn't a space but actually your quotes around "NCHMD". Notice how those in your comment are slanted and those in my comment are straight up and down? The slanted ones are called "Smart Quotes" which (ironically enough) are not recognized as valid characters in a Smartsheet formula. When it started working, you must have retyped them as regular quotes.

All comments

jjg279

If you are going to put it in a dashboard I would recommend using a report, then you can just filter the report by client. Once you get your first one created then just "save as new" (it will keep your settings) and swap the filter for the client.

Alternatively you could add that requirement to your COUNTIFS statements:

=COUNTIFS({Created Date Range}, IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()) - 1, [Client Name]:[Client Name], [Client Name]@row, )

Hello!

So Last year for just Syngergytest would be:

=COUNTIFS({Created Date Range}, IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()) - 1, {Client Name}, "Synergytest")

=COUNTIFS({Created Date Range}, IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()) - 1, {Client Name}, [Client Name]) →where [Client Name] is in the metric sheet and matches data in the outside sheet.

One more suggestion/note you can consider:

Thanks to you both for replying so quickly. @Janae G, that is exactly what I was trying to do, simply add a filter by client name. Your first suggestion worked like a charm.

Last issue. Where would I add that same Client Filter to in the below function? I'm trying to pull the same information but from last week, month and year:

This returns the actual number but not by client.

@Malcolm Jackson I would suggest starting with simplifying your formula. That will make it much easier to determine where to put your additional range/criteria set.

=COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY(-7)))

The above does exactly what your longer IF/COUNTIFS/COUNTIFS formula will do but is much easier to maintain and update.

Now that you only have a single COUNTIFS, adding the new range/criteria set for the client would be the same as your other COUNTIFS.

@Paul Newcome that worked, I appreciate the assistance. I'm almost to the finish line. Using your same logic, I'm trying to calculate resolved tickets from last week, month and year but I'm getting the error #UNPARSEABLE…..I've triple checked my reference sheets and operators…what am I missing?

COUNTIFS({Resolved Date Range Client}, IFERROR(WEEKNUMBER(@cell ), 0) = WEEKNUMBER(TODAY(-7)), {Client Name NCHMD}, “NCHMD”)

@Paul Newcome - I'm good to go, I had a blank space from the copy/paste. I really appreciate everyone's assistance.

@Paul Newcome - absolutely typed it out again lol. I see, yes, thanks for the additional review and knowledge.

Happy to help. 👍️

Glad it worked!

For Last Week, looks like you would need to add Client Name in both COUNTIFS if you use two:

=IF(WEEKNUMBER(TODAY()) = 1, COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell ), 0) = 52, {Created Date Range}, IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()) - 1, {Client Name}, "Synergytest"), COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell ), 0) = WEEKNUMBER(TODAY()) - 1, {Created Date Range}, IFERROR(YEAR(@cell ), 0) = YEAR(TODAY()), {Client Name}, "Syngergytest"))

If you go with the simplified formula from Paul above:

=COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell ), 0) = WEEKNUMBER(TODAY(-7)), {Client Name}, "Synergytest")

Which would also work, though you might still need the YEAR criteria in there for the last week of the year…

@Janae G. thanks for the follow up and help last week. I went with Paul's suggestion on this section, and it worked as designed. I appreciate the feedback.

Of course! I posted the comment several days ago… Smartsheet Community was acting weird and it got delayed. Glad Paul's worked!

@Malcolm Jackson,

@Janae G. has a good point about the year aspect. If you are going to have multiple years within your source data, you are going to want to work a YEAR function in like so:

=COUNTIFS({Resolved Date Range Client}, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY(-7)), IFERROR(YEAR(@cell), 0) = YEAR(TODAY(-7))), {Client Name NCHMD}, "NCHMD")

While the first/last week of the year won't make a difference if you leave that out, you could get skewed data if you have multiple years in there since it was originally just looking at the week number.

Lol, no worries. Your initial solution worked for me, so I used a combination of yours and his. You guys saved me so much time and effort.