I need help creating a formula that pulls information from two columns.

Hello All.

I need help creating a formula from two columns on one sheet and pushed to the summary sheet/dashboard with the totals.

The first column is a drop down menu for the order status (New, Processing, Billing, Completed, and Cancelled) and the second column contains a check or is unchecked. I am counting the drop down menu list with the unchecked rows. I have tried several of the formulas shared in previous discussions, but none have worked. I am only getting errors with the formats I have used so far.

Thanks,

Tammy

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @tschmidt

    It sounds like a cross-sheet COUNTIFS should work for you! A COUNTIFS Function is structured like this:

    =COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

    In your case, the first criteria would be the Status, and the second criteria is that a box is not checked, or is 0.


    For example:

    =COUNTIFS({Column with Status}, "New", {Checkbox Column}, 0)

    Or, if you have a Status column in your current metric sheet:

    =COUNTIFS({Column with Status}, Status@row, {Checkbox Column}, 0)


    See: Cross-sheet formulas

    Another option would be to create a Report with a filter that the box is unchecked, the Group the Report by the Status and use the Summarize function to count those sections. See: Redesigned Reports with Grouping and Summary Functions

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!