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

T C

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?

KPIs for Smartsheet Metrics.xlsx

Gillian C

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 :)

LookW

Here you go!

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

T C

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.

Gillian C

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!

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?

Gillian C

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 :)

T C

This took care of the issue. Thanks!