I am trying to find a formula that can count % ranges in a column. See the attached graph

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @William Evans

    The way I would do this is to set up a second, Metric sheet with cross-sheet formulas.

    One column would identify the range you're looking for (e.g. "0 - 20%" in one cell, "21 - 40%" in the next cell).

    Then you can use a COUNTIFS statement in a second column to look into your source sheet for your ranges, like so:

    =COUNTIFS({Percent Column}, <= 0.2)

    Then for the next range:

    =COUNTIFS({Percent Column}, > 0.2, {Percent Column}, <= 0.4)

    And the next...

    =COUNTIFS({Percent Column}, > 0.4, {Percent Column}, <= 0.6)

    And so on!


    Then you can use this Metric sheet as the source for your Chart. Here's more information:

    COUNTIFS Function / Create cross sheet references to work with data in another sheet

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!