Need help with a formula from two different columns
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!