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 Admin
    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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!