Formula Help

Hello,

I recently used this =SUMIFS formula and now that I have changed some VLOOKUPS the formula gives me a #NO MATCH error.

SUMIFS([Request Total]:[Request Total], [Employee ID]:[Employee ID], [Employee ID]@row, Date:Date, <=Date@row, Date:Date, >=DATE(2022, 7, 1), [Department Approval]:[Department Approval], "Approved")

I am calculating the total amount request by employee YTD.

Thank you!

Find more posts tagged with

Formulas

Cell linking

Accepted answers

All comments

jmyzk_cloudsmart_jp

@jessicam

Your SUMIF formula has no problem.

Since you use VLOOKUPS, what can happen is if you change the position of the columns in the lookup_table, it gives a #NO MATCH error.

For example, suppose you have VLOOKUP like

=VLOOKUP([Request ID]@row, {Request Approval Data Range 1}, 2)

Changing the order of the columns in the {Request Approval Data Range 1}, from

Request ID, Approval to

Approval, Request ID

will give you #NO MATCH errors.

If that is the case, I suggest using INDEX, MATCH unaffected by column order change.

https://www.smartsheet.com/content-center/best-practices/tips-tricks/3-formulas-look-up-data-smartsheet

jessicam

@jmyzk_cloudsmart_jp

Thank you for your insight. I actually figured out what the error was. Apparently the column need to have a formula

Now my SUMIFS formula works even with my adjustment in adding a new Fiscal Year column instead of the calendar date.

Thanks again!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.