IF and IFCOUNTS to reference across 2 other sheets

Nataly Allimonos
Nataly Allimonos ✭✭✭
edited 04/05/23 in Formulas and Functions

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

Tags:

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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!