Return a Yes or No on growing list depending on specific parameters.

[kbiro]

Hello!

I have a growing list with dates and serial numbers. I need a column to automatically return a yes or a no if the same serial number has shown up on the list within the last 30 business days from the previous call.

I know I'll probably need a Match and an IF statement, but I am unsure where to go from there as this list will continue to grow through the month.

Example is attached.

[Pete_UK]

Try this in C2:

=IF(COUNTIFS(A$1:A2,"<="&A2,A$1:A2,">="&A2-30,B$1:B2,B2)>1,"yes","no")

then copy down.

Hope this helps.

Pete

[etaf]

this may help
=COUNTIFS($A$2:A2,">="&A2-30,$B$2:B2,B2)
which will do a count and count 1 for itself and then if repeated count 2,3,4 etc

in an IF
=IF( COUNTIFS($A$2:A2,">="&A2-30,$B$2:B2,B2)>1 , "Yes", "No")

this is checking the date to see if the previous cells have a date between the cell and 30 days before and the same serial number

[etaf]

website crashed while posting

[kbiro]

That did the trick! Thanks!

[Pete_UK]

You're welcome - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete