Compare Columns Across Sheets
Hello,
I'm trying to reconcile a bank statement with our Cash Flow log. I'm trying to compare a value on one sheet (the Cash Flow Log) with a range on the other (the bank statement sheet). If found, I'd like it to return "Yes" on my Cash Log sheet. I'm getting the Unparseable error message. I did insert the reference column. Here's what I have:
Can anyone assist?
Answers
-
You are correct you need to use a COUNTIFS statement. When looking at cross sheets, to determine if a value on one sheet is part of a list on another, we can look for countifs values that are greater than zero - that means a value was found. If the countifs returns a zero, it means no match was found.
In the formula above, the syntax of the COUNTIFS is incorrect. COUNTIFS always have the syntax of =COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3....) with 1 to infinity number of pairs, but always a range, criteria pair. Notice the missing comma between the range and criteria in the formula above.
Try this
=IF({JPM Data Upload Range 2}, [Credit Amount]@row)>0, "Yes", "No")
To further build on your formula, as a good practice consider renaming the generic smartsheet cross sheet range number (for example {JPM Data Upload Range 2}to your actual source column name. This will help you and the community better understand your formulas.
cheers,
Kelly
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!