COUNTIFS FORMULA REFERENCING ANOTHER SHEET HELP

Melanie Sanders

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.

Jeff Reisman

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.

Jeff Reisman

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.