IF and IFCOUNTS to reference across 2 other sheets

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")))


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    "@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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!