COUNTIFS for cell value between 2 limits

Hi everyone,

I'm using the appended formula to count the number of times the cell values in a column fall between two limits.

=COUNTIFS({Summary sheet Family}, "Cups", {summary sheet install phase}, "Storage", {summary sheet Storage duration}, >=[Metric1,]26 < [Metric2]26)

The above formula works without error, but does not calculate correctly - it seems it uses the 2nd part of the formula only (< [Metric2]26))

I can use a helper column but does anyone know if it is possible to use "AND" so that both conditions can be used?

thanks


Tim

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Tim Dowden 

    Hope you are fine, please try the following formula:

    =COUNTIFS({Summary sheet Family}, "Cups", {summary sheet install phase}, "Storage", 
    {summary sheet Storage duration},AND(@cell >=[Metric1]26,@cell < [Metric2]26))
    

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Tim Dowden 

    Hope you are fine, please try the following formula:

    =COUNTIFS({Summary sheet Family}, "Cups", {summary sheet install phase}, "Storage", 
    {summary sheet Storage duration},AND(@cell >=[Metric1]26,@cell < [Metric2]26))
    

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Thanks for your prompt reply Bassam, your solution worked perfectly

    cheers


    Tim

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/08/21

    @Tim Dowden

    Excellent, i will be happy to help you any time.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!