COUNTIFS with reference another sheet

Olivier Pillon
Olivier Pillon ✭✭✭
edited 12/09/19 in Formulas and Functions


I'm looking for the right sentence to count the lines from a second sheet how meets my criteria.

This one doesn't work: #UNPARSEABLE 

=COUNTIFS({Inventory Asset - Africa & Middle East Range 1}, ([TYPE]1:[TYPE]56,"Physical",[WARRANTY EXPIRATION]1:[WARRANTY EXPIRATION]56, "EXPIRE IN 24 MONTHS"))

I just want to count the lines where the column [TYPE] is "Physical" and the [WARRANTY EXPIRATION] is "EXPIRE IN 24 MONTHS"

Thank you for your help






  • Hello Olivier,

    I'd recommend removing the extra parenthesis around your other cell references in your formula. Example:

    =COUNTIFS({Inventory Asset - Africa & Middle East Range 1}, [TYPE]1:[TYPE]56,"Physical",[WARRANTY EXPIRATION]1:[WARRANTY EXPIRATION]56, "EXPIRE IN 24 MONTHS")

  • thank you Shaine but the problem still exist.


  • Hi Olivier,

    Apologies—I initially misunderstood what you're wanting to do with your function in my prior response. COUNTIFS syntax is as follows:

    COUNTIFS(range1, criterion1, range2, criterion2)

    If both of the ranges you're wanting to count exist on another sheet, you'll want to remove the cross sheet reference that you have, and create two cross-sheet references—one for your TYPE column range and another for your WARRANTY EXPIRATION range. Example:


    More on COUNTIFS in the help center:

    Let me know if that's not what you're looking for and I can advise further.

  • Thank you so much Shaine !!!


    It works fine now.



  • Thank you all. very useful thread.

    Thank you Shane - your answer helped a lot. I did reference the column in the reference sheet twice and it worked perfectly

    =COUNTIFS({ Tracker Range 1}, "New", { Tracker Range 2}, "Medium")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!