How do I COUNT items according to the criteria of Date and Category?
I am try to create a metric to understand how many items were shipped in a month, according to the product type.
Can some one help with the formula from the example below?
Here to learn, willing to help!
Best Answer
-
Hi @T C
There are a few things that you can check:
1 Are all the columns of type Text/Number? ([Category], [Promised Delivery Date Month],[Actual Delivery Date Month])
2 You may have some blank date columns if you have a row with dates not added yet, causing the [Promised Deliver Date Month] and [Actual Deliver Date Month] to show as #INVALID DATA TYPE …see below
If this is the case then change the following formula to:
[Promised Delivery Date Month] =IFERROR(MONTH([Promised Delivery Date]@row), "")
and
[Actual Delivery Date Month] = IFERROR(MONTH([Actual Delivery Date]@row), "")
And the error no longer occurs because the formula now returns a blank cell if there are no dates.
Hope that helps :)
Answers
-
Here you go!
The Column 5 formula criteria is just the month number, so I made January "1", February "2", March "3", etc. -
Thank you @LookW.
A couple of things:-I would like to return the month and year; to not be confusing in the future when the year changes.
-the count has to take into account the Category
-Also, the second formula that is needed is the most difficult one for me to nail down.Here to learn, willing to help!
-
Hi @T C
Try taking a look at
COUNTIFS Function | Smartsheet Learning Center
My suggestion would be to create 3 helper columns:
[Promised Delivery Date Month] with a column formula of =MONTH([Promised Delivery Date]@row)
[Late] with a column formula of =IF([Actual Delivery Date]@row > [Promised Delivery Date (+7)]@row, "Yes", "No")
[Actual Delivery Date Month] with a column formula of =MONTH([Actual Delivery Date]@row)
Then the formula for the red circled cell above would be
=COUNTIFS(Category:Category, "Shirt", [Promised Delivery Date Month]:[Promised Delivery Date Month], 1)
And the blue circled formula would be
=COUNTIFS(Category:Category, "Shirt", Late:Late, "No", [Actual Delivery Date Month]:[Actual Delivery Date Month], 1)
Then all you need to do is change the Month Number and Category you want to filter for.
Hope this helps!
-
Hey @Gillian C ,
I am receiving an "Invalid Data Type" for the following formulas:=COUNTIFS(Category:Category, "Shirt", [Promised Delivery Date Month]:[Promised Delivery Date Month], 1)
and
=COUNTIFS(Category:Category, "Shirt", Late:Late, "No", [Actual Delivery Date Month]:[Actual Delivery Date Month], 1)
Everything else is working like a charm. Any suggestions?
Here to learn, willing to help!
-
Hi @T C
There are a few things that you can check:
1 Are all the columns of type Text/Number? ([Category], [Promised Delivery Date Month],[Actual Delivery Date Month])
2 You may have some blank date columns if you have a row with dates not added yet, causing the [Promised Deliver Date Month] and [Actual Deliver Date Month] to show as #INVALID DATA TYPE …see below
If this is the case then change the following formula to:
[Promised Delivery Date Month] =IFERROR(MONTH([Promised Delivery Date]@row), "")
and
[Actual Delivery Date Month] = IFERROR(MONTH([Actual Delivery Date]@row), "")
And the error no longer occurs because the formula now returns a blank cell if there are no dates.
Hope that helps :)
-
This took care of the issue. Thanks!
Here to learn, willing to help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!