COUNTIFS FORMULA REFERENCING ANOTHER SHEET HELP

Hi Guys, I have been trying to figure out why my formula is not working. I am referencing a sheet named LATE VENDOR DELIVERIES and looking at the columns in that sheet LOCATION # AND NAME and also CLAIM STATUS. I don't see where it references the other sheet in my formula so not sure if that is the problem.


=COUNTIFS({Location # and Name}:{Location # and Name},LOCATION@row,{Claim Status}:{Claim Status}, "Complete - Credit Received")

I get an #UNPARSEABLE error when I hit enter. Please help.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    When you create a reference to a column in another sheet, the range name includes the entire column. Therefore, in your COUNTIFS, just putting {Location # and Name} as the range to search is enough. You don't need to put it in {Location # and Name}:{Location # and Name} format.

    =COUNTIFS({Location # and Name}, LOCATION@row, {Claim Status}, "Complete - Credit Received")
    

    Also make sure you add a space after commas.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    When you create a reference to a column in another sheet, the range name includes the entire column. Therefore, in your COUNTIFS, just putting {Location # and Name} as the range to search is enough. You don't need to put it in {Location # and Name}:{Location # and Name} format.

    =COUNTIFS({Location # and Name}, LOCATION@row, {Claim Status}, "Complete - Credit Received")
    

    Also make sure you add a space after commas.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!