How do I COUNT items according to the criteria of Date and Category?

T C
T C ✭✭✭✭
in Formulas and Functions

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?

image.png
KPIs for Smartsheet Metrics.xlsx

Here to learn, willing to help!

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Gillian C
    Gillian C Overachievers
    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

    image.png

    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.

    image.png

    Hope that helps :)

    · Share on FacebookShare on Twitter

Answers

  • LookW
    LookW ✭✭✭

    Here you go!

    The Column 5 formula criteria is just the month number, so I made January "1", February "2", March "3", etc.

    image.png

    · Share on FacebookShare on Twitter
  • T C
    T C ✭✭✭✭
    edited 08/05/24

    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!

    · Share on FacebookShare on Twitter
  • Gillian C
    Gillian C Overachievers

    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!

    · Share on FacebookShare on Twitter
  • T C
    T C ✭✭✭✭

    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!

    · Share on FacebookShare on Twitter
  • Gillian C
    Gillian C Overachievers
    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

    image.png

    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.

    image.png

    Hope that helps :)

    · Share on FacebookShare on Twitter
  • T C
    T C ✭✭✭✭

    This took care of the issue. Thanks!

    Here to learn, willing to help!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!