Help with SUMIFS and a Does Not Equal Statement

Columns: Region Sales Price

I want a sum of the SALES PRICE Column for a specific region, but not to include "#NO MATCH" or "Reviewing" in the sum of the Sales price column. I just cannot get it to work.

=SUMIFS({Summary Data Sale Price}, {Summary Data Region}, "South Atlantic", {Summary Data Contract Status},<>"CW Reviewing/Negotiating", {Summary Data Contract Status},<>"#NO MATCH" )

I know I am issing something but I cannot find that "something"

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hello @french2s

The #NoMatch is an error message and not a searchable text string. There are a couple of work arounds that will get you what you need. I'll explain one of them

You can go to the formula in your sheet that is throwing the error and wrap that formula in an IFERROR. Literally, you wrap your existing formula in your Status column with an IFERROR function. You will indicate what you want the cell to do/say if an error occurs. As written, I am leaving the cell blank, as denoted by the double quotation marks.

=IFERROR(entire current STATUS column formula, including all of the parentheses, "")

Your SUMIFS should then become

=SUMIFS({Summary Data Sale Price}, {Summary Data Region}, "South Atlantic", {Summary Data Contract Status},<>"CW Reviewing/Negotiating")

will this work for you?

Kelly

french2s

I do not want the #NO MATCH to say anything. I actually want it to show that error. I do not want to have to re-vamp all my spreadsheets that are linked together and re-do all the formulas and there are at least 4 sheets and many, many columns, so I am ok with the #NO MATCH.

What you put in your SUMIFS is what I have and it does not work. Can we do something else?

Kelly Moore

Hey @french2s

Does your Status column always have data in it (for the data that you want summed)? Can it ever be blank (in your summed dataset)?

If no, try this

=SUMIFS({Summary Data Sale Price}, {Summary Data Contract Status}, ISTEXT(@cell), {Summary Data Region}, "South Atlantic", {Summary Data Contract Status},<>"CW Reviewing/Negotiating")

We can also try this one

=SUMIFS({Summary Data Sale Price},{Summary Data Region}, "South Atlantic", {Summary Data Contract Status},IFERROR(@cell<>"CW Reviewing/Negotiating", "NoMatch"))

french2s

Kelly - I think you are right. I need to adjust the first formula with an IFEFFOR. So, here is the formula that I have. I am pulling Contract Status from my Master Smartsheet:

=VLOOKUP([Store #]@row, {Company Master Property List Range 5}, 21, false)

Of course, if there is a property where there is no contract status, it is returning #NO MATCH

I tried the IFERROR wrap and it did not work. Is there a missing operator since this is a VLOOKUP?

If I can get this figured out, I only have two more calcuations it figure out formulas!

Thank you

Kelly Moore

Hey

I was pretty sure you needed to fix the errors.

The IFERROR function has the syntax of IFERROR(value, value if error). The value is your current formula. The value if error is what happens when an error exists. There must be some response for what happens if there is an error.

=IFERROR(VLOOKUP([Store #]@row, {Company Master Property List Range 5}, 21, false),"NoMatch")

The "NoMatch" string, since it is now text, will be searchable within your SUMIFS formula

Does this work for you?

Kelly

https://help.smartsheet.com/function/iferror

french2s

So, now it is showing "No Match" for every cell, when it should be pulling the contract status from my Master Smartsheet and only returning the text "No Match" if there is an error in the cell in column I am pulling from.

Contract Status column is column 21 in the Master Smartsheet
In my summary data Smartsheet, I have a column called: Contract Status. I used the following formula to pull in the status from the master sheet: =VLOOKUP([Store #]@row, {Company Master Property List Range 5}, 21, false)
Because some of those properties do not have a contract status, it is giving a "#NO MATCH" error.
I need to solve for this error to bring the Contract Status into this summary sheet, but change the error to text "No Match"
You recommended: =IFERROR(VLOOKUP([Store #]@row, {Company Master Property List Range 5}, 21, false),"No Match")
This worked, but it turned all the cells to the text "No Match". It is not pulling thru the contract status for those that have one.
I did notice that this column in the summary Smartsheet was a dropdown, but I changed it to text since it should be pulling in whatever is in the Master Smartsheet, but that did not change anything.
What I need is 1) to pull thru the contract status in the master sheet BUT 2) if an error is returned, it returns "No Match". (Note: The Contract Status in the Master Smartsheet is pulled from the Status in my Transaction Smartsheet. I am using the Master to collect ALL data on my locations, then pulling just a few data points into my Summary Smartsheet)

Not sure if this helps, but wanted to give you as much data as possible. My Transaction Smartsheet only contains transactions, so the Master Smartsheet will show the error is there is no transaction. Hope that makes sense.

Kelly Moore

Hey

Hmmm. If you put your original formula, without the IFERROR back in your sheet - does it work? We made no changes to that formula. Have you made any changes to your sheet that would affect the number of columns in your VLookUp table?

As a test, would you try this formula instead of your VLookUp. You will need to create these two cross references. Note these references are each single columns - not tables. The column numbers I give refer to your VLookUp table above.

=INDEX({Company Master Property List Column 21}, MATCH([Store #]@row, {Company Master Property List Store # column}, 0))

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.