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
- 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!