IF and IFCOUNTS to reference across 2 other sheets

Nataly Allimonos

Hey community -

We are migrating from excel to using Smartsheets. We have a lot of formulas that are being migrated over as well within these sheets.

Here's the fomula working very well in excel:

Sheet names referenced within the same book in excel are:

1. Ignore Opps
2. Account Assignment_Round
3. 1.Refresh Customer Doc

=IF(B8="","-",IF(COUNTIFS('Ignore Opps'!$D:$D,'Account Assignments_Round '!D8)>0,"-",IF(COUNTIFS('1. Refresh Customer Doc'!$A:$A,'Account Assignments_Round '!B8)>0,"-","Net New")))

The idea is, on a per row basis, if cell "B8" is blank add a "dash." But, if this cell is not blank cross-refence with these two sheets, if this cell value exists in both of these add a "dash," if this cell value does not exist in these two sheets, label "NET NEW"

My thought process was that I can replicate this on smartsheet but it's coming up as unparsable. In reading the formulas instructions on smartsheets, I'm definitely approaching this incorectly. This is what I tried below, any help would be gladly appreciated!

=IF([Account Number]7="","-",IF(COUNTIFS({Ignore_Opps_OppNumber},[Opportunity Number]@Row)>0,"-",IF(COUNTIFS({Customer_Doc_AccountID},[Account Number]@Row>0,"-","Net New")))

Paul Newcome

"@row" is case sensitive and must be all lower case.

Additionally, you are missing a closing parenthesis there after the very last "@row" to close out the COUNTIFS function.

But it looks like otherwise it should work for you.

Paul Newcome

"@row" is case sensitive and must be all lower case.

Additionally, you are missing a closing parenthesis there after the very last "@row" to close out the COUNTIFS function.

But it looks like otherwise it should work for you.

Nataly Allimonos

Thanks @Paul Newcome ! Appreciate the view on this; works like a charm now!

Paul Newcome

Happy to help. 👍️