We have products with expiration dates and we need a cell to count them in a range of days.

Ian777

We have a column with expiration date that is put in manually by an employee with the rest of the data when received. Currently we have conditional formatting to highlight the products expiring within 30 days via the row turning yellow. We need a cell that will automatically count and display a value for all yellow (expiring within 30 days) and ignore already expired (red) products. Can this or something similar be done? Thank you in advance for any help.

Dale Murphy

@Ian777 My approach would be to add a column that I would use to calculate days to expiration.

It would be a column formula along the lines of =[Expiration Date]@row - today()

Then you just count how many [Days to Expiration] are greater than 0 and less than 30. Something like:

=COUNTIFS([Days to Expire]:[Days to Expire], >-1, [Days to Expire]:[Days to Expire], <30)

Where do you plan to put that result? Do you have a dashboard that can hold that metric? Perhaps build a summary field (that is where that formula came from) and use it on a report?

dm

Ian777

Thank you so much for trying to help. Here is a screenshot of a an example for our project without any sensitive information.

Blue cells in the column "Location Specific QTY" are summing up how many of the material is in each room specifically. This is working well and to do this I used the following.

=SUMIF(Location:Location, Room Number here, ([Complete Count/QTY]:[Complete Count/QTY]))

The blue cell in the "Complete Count/QTY" column is summing up a total for the entire column using the following.

=SUM([Complete Count/QTY]:[Complete Count/QTY])

What I have not been able to figure out is how to create a cell that will not just count how many rows of material will expire in 30 Days, but the sum of the "Complete Count/QTY" of all rows that will be expiring in 30 days.

For example using the image above, the cell would ideally tell us 7 not 1 indicating the one row with materials expiring. Any help would be greatly appreciated. Thank you so much in advance.

Ian777

This is as close as I have come. Unfortunately it is not summing from the current day to 30 days out, but instead summing all in that particular month.

=SUMIF([Expiration Date]:[Expiration Date], (IFERROR(MONTH(@cell), 0) = 2), [Complete Count/QTY]:[Complete Count/QTY])

Also, even if this worked we would have to create a cell for each month which is a little cumbersome for the sheet.

Side note I have only been learning Smartsheet for a week now so I have a lot left to learn.

Ian777

For anyone needing this same function it has been solved!

=SUMIF([Expiration Date]:[Expiration Date], (IFERROR(MONTH(@cell), 0) = 2), [Complete Count/QTY]:[Complete Count/QTY])

Ian777

CORRECTION- I copied the wrong formula

=SUMIFS([Complete Count/QTY]:[Complete Count/QTY], [Expiration Date]:[Expiration Date], [Expiration Date]@row <= TODAY() + 30, [Expiration Date]:[Expiration Date], [Expiration Date]@row >= TODAY())