Newbie Question: Want to Count "Product Name" but only if its date is in the future

kim_e

Hello!

I am very new to Smartsheets and I am trying to get the best use out of it. But, I am little stumped.

I have a COUNTIF formula to count how many times a product name is mentioned in the sheet and then I used conditional formatting to highlight it if there is more than one time the product is mentioned.

=COUNTIFS([Product Name]:[Product Name], [Product Name]@row)

"If Helper is greater than one and Product Name is not blank than apply this format to the entire row"

I would like to have a set of formulas/ conditional formatting that highlights product names ONLY if they are in the future more than once. But, my current formula calculates all times the product name is mentioned in the sheet.

EX: If "Apples" is listed twice, for March 31st and April 8th, I would want that highlighted. I would NOT want it highlighted if there is "Oranges" listed January 8th and June 16th, because there is only one "Oranges" in the future.

Apologies if I am not making much sense! But I am very new to formulas and any advice would be greatly appreciated!

Genevieve P.

Hi @kim_e

Thank you for outlining your scenario! Yes, you can do this with a COUNTIFS in a helper column in your sheet.

What I would do is have an IF statement to see if the date in this row is in the future. If it isn't, then we want a blank cell so the row won't be used in Conditional Formatting. But if the date is in the future, then run the COUNTIFS to see if there's more than one in the future.

Try something like this:

=IF([Date Column]@row < TODAY(), "", COUNTIFS([Product Name]:[Product Name], [Product Name]@row, [Date Column]:[Date Column], >= TODAY()))

Then your conditional formatting rule can be based on if that cell is Greater than 1:

Here's more information about the TODAY Function.

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @kim_e

Thank you for outlining your scenario! Yes, you can do this with a COUNTIFS in a helper column in your sheet.

What I would do is have an IF statement to see if the date in this row is in the future. If it isn't, then we want a blank cell so the row won't be used in Conditional Formatting. But if the date is in the future, then run the COUNTIFS to see if there's more than one in the future.

Try something like this:

=IF([Date Column]@row < TODAY(), "", COUNTIFS([Product Name]:[Product Name], [Product Name]@row, [Date Column]:[Date Column], >= TODAY()))

Then your conditional formatting rule can be based on if that cell is Greater than 1:

Here's more information about the TODAY Function.

Let me know if this works for you!

Cheers,

Genevieve

kim_e

https://community.smartsheet.com/discussion/comment/322694#Comment_322694

Thank you very much for your help! That formula worked out pretty well.

It does seem to have an issue with dates that have just past. EX: products under 3/8/22 seems to be counted, occasionally. I am hoping that is just a synchronization issue with Smartsheets and it will figure it out.

Thank you again!

Genevieve P.

Hi @kim_e

This should immediately work for all dates, based on Today's date. Can you try just the first part of the formula?

=IF([Date Column]@row < TODAY(), "Past", "Future")

If you're seeing incorrect results, could you post a screen capture, but block out sensitive data?

Thanks!

Genevieve