# Not sure what kind of formula to use

Hello,

I am creating a project deconfliction sheet. I have a column where a date is manually entered. I would like to create a flag when any of the cells (so far 13) in a row that contain the same date will check the box.

Sharon C

• Create a check box column containing the formula:

=IF(COUNTIF(date:date, =date@row) > 1, 1, 0) where date:date is the column with the dates you want to check and date@row is the date in the row being checked.

The formula will flag all duplicates. If you only want to flag the newest duplicate than you can use:

=IF(COUNTIF(date@row:date\$50, =date@row) > 1, 1, 0) where date\$50 is someplace below the last row in your sheet. If you go down too far you'll get an error. This formula starts at your current entry and looks down only. If you're adding New rows to the bottom you'd use date1:data@row so it looks up.

Work?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Thank you for the quick response. I don't think this will work. I am looking to get this to check if all the cells from V-Lift thru Rebrands contain the same date as the sample date.

• I am getting an Unparseable. Would I need to add an OR somewhere in there?

• I got this to work:

=IF(AND(VLift@row = [Sample Install Date]@row, OR([PDI Minn]@row = [Sample Install Date]@row, Chase@row = [Sample Install Date]@row, Valero@row = [Sample Install Date]@row, Irving@row = [Sample Install Date]@row, Frys@row = [Sample Install Date]@row, BP@row = [Sample Install Date]@row, Marathon@row = [Sample Install Date]@row, [P66]@row = [Sample Install Date]@row, Rebrands@row = [Sample Install Date]@row)), 1, 0)

• Excellent! The OR function means that if any of the criteria in the parens following is true then the statement is true. So it's looking to see if vlift = sample data and then whether any of the other dates match sample data. If 1 of them matches then it will check the box. Is that what you're trying to do? If you want all the dates to match then remove the OR(). If you get an error confirm that all the cells references are named correctly. When you highlight the formula all of the date cells in the row should be highlighted.

Glad you're able to make it work.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!