Need help with a formula from two different columns

amccue121

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)

Genevieve P.

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