Formula with severals needs
Hello Community !
i'm working on a formula to count how many machine of a brand have been sold. so i start with :
=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New")
this formula is working well.
But i want to add specific conditons to my formula and this is where it's not working when i'm writing it. Can you help :
To be count it need to be :
- New ( from column NEW OU USED)
- Yanmar ( from column Brand)
- "Current "or "in order" ( from my status column)
- and between a range of date, between nov 1 2022 to 31th october 2023 ( from my Date column)
Can you help me please.
thanks a lot.
Best Answer
-
You are almost there @Cynthia Guay Just keep going with conditions in your COUNTIF.
You already have the first two things you need
- New ( from column NEW OU USED)
- Yanmar ( from column Brand)
With this, your current formula:
=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New")
In my sample of your data, this formula counts the 6 rows in yellow.
Next, you need to add another condition for the Status, but this one needs an OR in the criterion to count both "Current" and "In order" statuses, to meet your third requirement:
- "Current "or "in order" ( from my status column)
For this, you use:
Range: Status:Status
Criterion: (OR(@cell = "Current", @cell = "In order"))
So the formula becomes this (new part in bold):
=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")))
In my sample, this gives you 4 rows.
Finally, we need another two conditions for the date range.
- and between a range of date, between nov 1 2022 to 31th october 2023 ( from my Date column)
Firstly, add a condition for on or after November 1, 2022.
For this, you use:
Range: Date:Date
Criterion: >=DATE(2022, 11, 1)
So the formula becomes this (new part in bold):
=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")), Date:Date, >=DATE(2022, 11, 1))
In my sample, that results in 3 records
And then, add one last condition, for on or before October 31, 2023.
For this, you use:
Range: Date:Date
Criterion: <=DATE(2023, 10, 31)
So the formula becomes this (new part in bold):
=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")), Date:Date, >=DATE(2022, 11, 1), Date:Date, <=DATE(2023, 10, 31))
In my sample, that results in 2 records.
Answers
-
You are almost there @Cynthia Guay Just keep going with conditions in your COUNTIF.
You already have the first two things you need
- New ( from column NEW OU USED)
- Yanmar ( from column Brand)
With this, your current formula:
=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New")
In my sample of your data, this formula counts the 6 rows in yellow.
Next, you need to add another condition for the Status, but this one needs an OR in the criterion to count both "Current" and "In order" statuses, to meet your third requirement:
- "Current "or "in order" ( from my status column)
For this, you use:
Range: Status:Status
Criterion: (OR(@cell = "Current", @cell = "In order"))
So the formula becomes this (new part in bold):
=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")))
In my sample, this gives you 4 rows.
Finally, we need another two conditions for the date range.
- and between a range of date, between nov 1 2022 to 31th october 2023 ( from my Date column)
Firstly, add a condition for on or after November 1, 2022.
For this, you use:
Range: Date:Date
Criterion: >=DATE(2022, 11, 1)
So the formula becomes this (new part in bold):
=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")), Date:Date, >=DATE(2022, 11, 1))
In my sample, that results in 3 records
And then, add one last condition, for on or before October 31, 2023.
For this, you use:
Range: Date:Date
Criterion: <=DATE(2023, 10, 31)
So the formula becomes this (new part in bold):
=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")), Date:Date, >=DATE(2022, 11, 1), Date:Date, <=DATE(2023, 10, 31))
In my sample, that results in 2 records.
-
@KPH , ho my god ! You can't imagine how long i work on it to make it work and i was way far from your result. Thank you so much for taking the time + with all the explanations, i can't wait to apply it :D.
-
You were 40-50% of the way there @Cynthia Guay and just needed a little nudge in the right direction. I hoped an explanation would be more helpful than an answer - now you can apply the same process to any new formula and you'll be flying! Good luck!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!