Not sure why this formula is not working

BrendonL

Hi all,

I have this issue where I'm trying to have Smartsheet automatically respond no to a column if the same person was requested before and said no already. IF they said no, the row would copy itself to the bottom and replace the one that said no's info with the next one requested. Thats where I would like smartsheets to automatically say no on behalf of the next one requested if they were the same person as before. One part of the formula I am trying to build will check the event number and the email to see if they are more than 1 and if they are then to check a box. the formula looks like this.

However, it is saying no match when the event numbers are the same and the emails are the same. I am not sure what is wrong with the formula. Any advice would be helpful. Thanks!

Mark Rojas

@BrendonL

My first suggestion would be to wrap that in an =IFERROR([Your massive if statement],"")

~MR

BrendonL

Hi,

I figured out that the volunteer email part is the part that isnt working:

however, I still dont know why this is not working…

Mark Rojas

@BrendonL

Try =COUNTIFS([Volunteers: Speakers Email]:[Volunteers: Speakers Email], @cell = [Volunteer: Speakers Email]@row)

~MR

BrendonL

@Mark Rojas I figured out that it is saying no match because the the speakers emails column is populated by a formula, and I guess the countif formula is having issues with that. Now I just need to figure out the best way of copying the value from the speaker formula to a new column so that the formula for the countif would work.

Mark Rojas

@BrendonL

That's really weird, I have tons of cross sheet formulas that index contact lists so I'm not sure why it would be throwing an error. Does the formula pulling in the emails ever throw an error? Can you share that formula?

~MR

BrendonL

It doenst. Here is that formula. Its basically a bunch of if then statements to cycle through a couple IDs.

Mark Rojas

@BrendonL

My first suggestion would be to wrap that in an =IFERROR([Your massive if statement],"")

~MR

Paul Newcome

If the NO MATCH error is present in the contact column, any formula (including a countifs) referencing the contact column will also show that error. You need to remove that error from the contact type column using an IFERROR statement.

BrendonL

That worked @Paul Newcome @Mark Rojas! Thanks alot.

-Brendon Li

Mark Rojas

@Paul Newcome

I gotta say its an honor to have you agree with my answer. Your work has saved a ton of my projects thank you!

@BrendonL Happy to help!

~MR