Cross sheet/ MATCH formulas

Hello everyone,

I am working on a few new smartsheets that tracks our sites incident reporting data.

I know I can work a COUNTIFS formula to do what I want, but i need the data in the summary to formulate based on the criteria at the top (circled below)

I need the 2 sheets to cross check each other and match weeks, periods and years.

So below, on the first top left "Accidents weekly total" table, The first aid box will need to count anything that is a first aid, that occurred in 2021, period 7, week 27.

, and for health care and lost time I need them to calculate the same, but for "Health care" and "Lost time" instead of "first aid", but also not equal to "Denied" in the claim status column

Here is the sheet I am looking to pull from. Claim status is where you would find the "denied" choice

I cannot show the data due to sensitive content

Once i know how to get the data to match between sheets, I should be good to go from there

TIA!

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

@Jessica Howell

Hope you are fine, please call me on my Email because I need a sample copy of your sheets and I will try to create this application for you

My Email: bassam.k@mobilproject.it

Andrée Starå

Hi @Jessica Howell

I hope you're well and safe!

I'd recommend using the Sheet Summary section to either make the selection or at least replicate the selection so you can then use that as a reference in all formulas.

An example of the formula would look something like this.

=COUNTIFS({Range Year}, IFERROR(YEAR(@cell),0)=Year#, 
{Range Week},IFERROR(WEEKNUMBER(@cell),0)=Week#, {Period Range} and so on.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jessica Howell

@Andrée Starå

Thanks Andree! I am well, hoping you are too.

I will give this a shot. Will this formula update the data when I change the circled criteria ? (manually) It wont always be the same every week

Basically, when i change the year, period and week at the top, i need the fields below to update accordingly

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.