Need help with a formula from two different columns

Options

I am trying to do an order count between my on net orders that have completed. I have used the following formulas and nothing seems to work. Seeing if anyone can assist me?

=COUNTIF([Order Type],= "On Net"]:[Order Milestone], ="Complete")

=COUNTIF([Order Type],= "On Net"):[Order Milestone], ="Complete")

=COUNTIF([Order Type],= "On net"):([Order Milestone], ="Complete")

=COUNTIF({Order Type}, "On net", {Order Milestone}, "Complete")

=COUNTIF({Order Type}, "On net", {Order Milestone}, Complete)

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @amccue121

    You're very close! A few notes:


    1. COUNTIFS

    Since you have multiple criteria ("On Net" and "Complete") then you'll want to use COUNTIFS plural, with an S, instead. See: COUNTIFS Function


    2. References: in the same sheet or cross-sheet?

    If you're creating the formula in the same sheet, then you'll want to reference columns in square brackets. For example, if you're looking only in row 1 for the Order Type, the reference would be:

    [Order Type]1

    If you're looking for a range of rows in that column, you add a colon after the start of the range, then list the column name again and the end of the range. So if I was looking through rows 1 - 5, it would be:

    [Order Type]1:[Order Type]5

    In your instance, you want to look through the entire column, regardless of row numbers. That way the formula continues to calculate as new rows are added. This means we don't want those numbers after the column name at all, so you list the column name twice:

    [Order Type]:[Order Type]

    See: Create a Cell or Column Reference in a Formula

    Cross sheet references are the ones {in these}, and you manually select the column from a pop-up window. Here's more information: Create cross sheet references to work with data in another sheet


    3 . Full formula example:

    =COUNTIFS([Order Type]:[Order Type], "On Net", [Order Milestone]:[Order Milestone], "Complete")


    Let me know if this makes sense and works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!