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:
- Ignore Opps
- Account Assignment_Round
- 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
-
"@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.
Answers
-
"@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.
-
Thanks @Paul Newcome ! Appreciate the view on this; works like a charm now!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!