SUMIF #NOMATCH Error

Chloe Rose

Hi,

I'm running into issues when trying to use as SUMIF function in my sheet summary. I need to have it add together all the percentages"Capacity Rating." The goal is to be able to create a SUMIF for each project manager in the division so we can see what their workload capacity is before assigning projects. I've used the SUMIF to pull from the "Delivery PM Email" column and then the "Capacity " column. The percentage in the capacity rating column is being generated by a nested if function and my SUMIF In the summary sheet is as follows:=SUMIF([Delivery PM]:[Delivery PM], "deliverypm@gmail.com", Capacity:Capacity)

I previously posted about this and the solution worked! However, when I moved everything into the new smartsheet workspace we created it now gives me the #NOMATCH error. It seems like it would be fairly straight forward since I used all the things I was given when I first had this issue but alas here I am with a different error code.

Any help would be so appreciated!

David Joyeuse

Hi @Chloe Rose - 8PCC

There's two things that can create a #NO MATCH in a SUMIF,

• First is one of your range isn't found by the function. Check your ranges in your summary sheet. But if they are on another sheet it should be this then: =SUMIF({Delivery PM}, "DeliveryPM@gmail.com", {Capacity})
• Second point is one of your cell you are trying to sum in the formula has a #NO MATCH in it. So you can also check that to make sure it doesn't comes from from the cells you are trying to add.

One last point. That's a huge long nested IFs formula you have here. I would suggest you to optimize this quite a bit, it'll speed the sheet a little bit and would be easier to update/debug.

Chloe Rose

Hi David,

Thanks for your reply. I believe that my range is found in the sheet. I'm confused because I have another sheet that is essentially identical and all the functions and formulas work just fine... I know that some of the cells I'm trying to sum don't have anything in them so the #NOMATCH there doesn't surprise me. however, I know that there are some cells that have both an email and something to sum but they still give me a a #NOMATCH error.

Paul Newcome

What exactly do you have in your [Delivery PM] column?

Chloe Rose

@Paul Newcome I have the email address of each Project Manager

Paul Newcome

Is it a contact type column or a text/number type?

Chloe Rose

It is a contact type column

Paul Newcome

Ok. In the column properties... Is the box checked to allow multiple contacts? Try making sure that box is unchecked.

Chloe Rose

Yes that box is unchecked. that's what makes this issue so weird! I have it working perfectly fine in a different smartsheet workspace with essentially all the same components and it works just fine but I cannot seem to find the issue within this environment.

Paul Newcome

Are you using cross sheet or same sheet? How are the emails entered?

Chloe Rose

The project manager emails are being INDEX/MATCHED from a different sheet. It's linked to the PMs supervisor, so when the supervisor is entered it auto-populates the project managers email.

Chloe Rose

In my other environment this is how I have it set up so that it's identical to the one I am currently working in. The Delivery PM column which is index matched from another sheet and auto populated. Contact type column and the box is unchecked. The capacity column (text/number) with the Nested IF.

and then the sheet summary SUMIF section:

And the formula works and spits out the summed percentage. That's why it's so confusing to me because I feel like I set both things up pretty identically.

Paul Newcome

Let's go all the way back to the true source data. The list that contains the emails being referenced by the INDEX/MATCH... Are those emails in a contact type column? Does that column have the box checked to allow multiple contacts? How are the emails entered into THAT list?

Chloe Rose

The emails are in Contact Type Column and the box isn't checked for it to allow multiple contacts. Someone else set up that sheet so it seems to be pulling it from some sort of directory that we have set up for our agency

Paul Newcome

Make sure that what is in the sheet and what you are typing into the formula is an EXACT match. It shouldn't make a difference, but let's go ahead and include matching on upper and lower case as well.

Insert this into a cell and make sure the email in the formula matches at least one email in the sheet (this is just for testing purposes).

=COUNTIFS([Delivery PM]:[Delivery PM], "deliverypm@gmail.com")

Chloe Rose

Well that formula works. I figured that if I added % into the SUMIF somehow it now doesn't give me a NOMATCH error but it just spits out 0.00%. Which also isn't correct.... So now my SUMIF is =SUMIF([Delivery PM]:[Delivery PM], "deliverpm@gmail.com", [Capacity %]:[Capacity %]) and that gives the 0.00%. which is maybe better than a NOMATCH but also not what I need obviously