formula to determine if multiple dates are "greater than" one date

[kg82]

Hello,

I have a sheet with 10 columns across of dates, the dates are when we
received certain documents. There is one final column that has the date by
which all of the documents should have been received.

How do I have excel compare all 10 dates to the 1 final date, and have it
make note if any of the dates (in the set of 10) are greater than the 1 final
date?

I do not want it to produce late/not late for each individual document
(which I am able to do easily), as the pack of documents is considered
invalid if even just one document was late, so I need it to look at all dates
at once and just produce a "this document pack is compliant" or "this
document pack is not compliant".

I had attempted using IF (eg if A2:F2>G2,"non compliant", "compliant") type
thing, but that produces a #VALUE for me, so I have to changesomething, I am
just not entirely sure what. Thanks!

 

[Bernie Deitrick]

Something like

=IF(SUMPRODUCT(--(A2:F2>G2))>0,"non compliant", "compliant")

HTH,
Bernie
MS Excel MVP

 

[nlp239]

Purely out of interest, would you please explain this statement:
=IF(SUMPRODUCT(--(A2:F2>G2))>0

Please note, I am NOT the OP.
Thanks

 

[Bernie Deitrick]

Youa re asking about: SUMPRODUCT(--(A2:F2>G2))>0

This part

A2:F2>G2

returns an array of True/False Values

This part

--(A2:F2>G2)

converts the true values to 1, the false values to 0

This part

SUMPRODUCT(--(A2:F2>G2))

sums those 1s and 0s to compare to 0. A Sum of 0 means that none where >
G2, any other sum means at least one was, and that feeds into the
conditional, and the rest of the IF.

But note, we could also have used

COUNTIF(A2:F2,">" &G2)

in place of the SUMPRODUCT. or

SUM(--(A2:F2>G2))

(but then we would need to array enter the formula using Ctrl-Shift-Enter.)

SUMPRODUCT was simply what came to mind first.

HTH,
Bernie
MS Excel MVP






Purely out of interest, would you please explain this statement:
=IF(SUMPRODUCT(--(A2:F2>G2))>0

Please note, I am NOT the OP.
Thanks