# 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!

• ✭✭✭✭✭✭

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

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
edited 07/23/21

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• @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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!