Formula Help

jessicam

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!

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!