Compare Columns Across Sheets

Options
Jennifer Dewhurst
Jennifer Dewhurst ✭✭✭
edited 08/27/21 in Formulas and Functions

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!