Compare Columns Across Sheets

Jennifer Dewhurst

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?

Kelly Moore

Hi @Jennifer Dewhurst

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

COUNTIFS Function | Smartsheet Learning Center

https://help.smartsheet.com/function/countifs

Counts the number of times all given conditions in their respective ranges are met.