excelexcel-formula

How to find duplicate values in one column while checking if a condition in another column is met

enter image description hereI work on a table with some cells, the value in "E" might be repeated once or more, but depending on the date value in column "H", I need to get specific text. But, I don't know how to write the formula.

What I need to do is:

IF) TOW CELLS HAVE THE SAME VALUE IN COLUMN"E" AND $H>TODAY(),"Valid Stream"

I wrote this formula, but it doesn't work:

IF(AND($E5=E:E,$H5>TODAY(),"Valid Stream"))

I don't need to check only "E5", but if any cell in column "E" is repeated, and because it might be repeated many times, I need to control that with the date in "H" if it's bigger than today().

"E5" is the first cell in the table.

enter image description here

Solution

  • Do you mean result like this?
    Conditions

    A formula in J5:

    =IF((COUNTIF($E$5:$E$12,$E$5:$E$12)>1)*($H$5:$H$12>TODAY()),"Valid stream","")

    It is a dynamic formula in a new Excel, in earlier versions requires Ctrl+Shift+Enter.

    Another variant requires all dates to be >=TODAY().

    Conditions2

    A formula in J5:

    =IF(COUNTIFS($E$5:$E$12,$E$5:$E$12,$H$5:$H$12,">=" & TODAY())>1,"Valid stream","")

    3rd attempt. Formula for a table and with requirement for all dates to be >=TODAY()

    =IF((COUNTIFS([Code],@[Code],[Issue Date],">=" & TODAY())>1)*(COUNTIFS([Code],@[Code],[Issue Date],">=" & TODAY())=COUNTIF([Code],@[Code])),"Valid stream","")

    enter image description here