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


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!