# Need help with monthly form tracking formula to Master sheet

Options
✭✭✭

Intake Form sheet for each site that has submitted their monthly activity form. Entries will be in this sheet for the entire current year, so each site ‘might’ have up to 12 monthly entries.

Each of the 60 sites can have multiple entries, the 2 sheets are linked with Site ID (form sheet) Site # Text (master sheet)

If the [Month] column is equal to “6” I need a formula in the Master sheet to show “Complete” otherwise show as “Missing” for that month (Jun 2024)

Master Sheet, tracking each of the 60 Sites when/if they completed the monthly activity form

I’ve tried so many combinations of INDEX/COLLECT, INDEX/MATCH and nothing seems to work. Been searching the Community for days. Any help would be greatly appreciated! ❤️

Tags:

• ✭✭✭✭✭✭
edited 07/09/24
Options

Hi @Dina K.

You can use the COUNTIFS formula like this;

=IF(COUNTIFS({Activity Entry Sheet : Site # Text}, "" + @cell = [Site # Text]@row, {Activity Entry Sheet : Year}, 2024, {Activity Entry Sheet : Month}, 7) > 0, "Complete", "Missing")

The"" + @cell = [Site # Text]@row condition is somewhat difficult. Since your site # Text has values like 00137 and 00144, just putting the {range}, criteria combination like the following is comparing 000137 to 137 and 00144 to 144. So, I added "" in front of [Site # Text]@row.

{Activity Entry Sheet : Site # Text}, [Site # Text]@row

When you get the COUNTIFS values that meet the Site #, Year, and Month criteria, you can determine which to show as "Complete" or "Missing" using the IF statement.

You could show a "Multiple Entries" warning IF the COUNTIF value is > 1. (To do that, you want a helper column like 2024 July Count, and make the formula easy like;

=IF([2024 July Count]@row>1,"Multiple Entries", IF([2024 July Count]="Complete","Missing"))

• ✭✭✭
Options

@ you are a GENIUS!!!!!!! Thank you so much! worked perfectly! You have been a great help to my co-worker as well. Thank you so much for helping me and other folks out on this Community Board ❤️

• ✭✭✭✭✭✭
Options

@jmyzk_cloudsmart_jp You should wear a cape! Thanks for all you do in the community. (I'm the co-worker) and you have helped me in the past and this will help with another solution I am creating.

MARLANA KALINOWSKI

Sr. Business Analysts / Smartsheet Solutions

National Pharmacy Services | Genoa Healthcare

• ✭✭✭✭✭✭
Options

Kind words like yours keep me going! Happy to help anytime.😁

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!