COUNTIFS with reference another sheet

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

Hello,

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

Olivier

 

 

Tags:

Comments

  • Shaine Greenwood
    Options

    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")

  • Olivier Pillon
    Options

    thank you Shaine but the problem still exist.

     

  • Shaine Greenwood
    Options

    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:

    COUNTIFS({TYPE}, "Physical", {WARRANTY EXPIRATION}, "EXPIRE IN 24 MONTHS")

    More on COUNTIFS in the help center: https://help.smartsheet.com/function/countifs

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

  • Olivier Pillon
    Options

    Thank you so much Shaine !!!

     

    It works fine now.

     

    ;-)

  • Sree Sankaran
    Options

    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!