# 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!

Tags:

• Overachievers

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

• ✭✭✭

Here you go!

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

• ✭✭✭✭
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!

• 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!

• ✭✭✭✭

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!

• Overachievers

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!