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

K

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!
 
B

Bernie Deitrick

Something like

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

HTH,
Bernie
MS Excel MVP
 
N

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Tracked changes by multiple people modified to one 0
Adding cells until they are greater than a lookup cell and returning a value 1
Synchronization problems while moving folders to new email 1
Mail Merge skipping records/sending one at a time 0
Problem with formula for finding last greater than/less than or equal to value 2
How can the font and size of (all) citation numbers be altered? 1
Default IF Date Is between 2 Dates then output additional column 1
vlookup and match first value greater than 4

Top