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.
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!
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.
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!
-
Thanks @Paul Newcome ! Appreciate the view on this; works like a charm now!
-
Happy to help. 👍️
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!