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:F2G2,"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!

Something like

=IF(SUMPRODUCT(--(A2:F2G2))0,"non compliant", "compliant")

HTH,
Bernie
MS Excel MVP


"kg82" wrote in message
...
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:F2G2,"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!

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

Please note, I am NOT the OP.
Thanks


On Oct 20, 12:26*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Something like

=IF(SUMPRODUCT(--(A2:F2G2))0,"non compliant", "compliant")

HTH,
Bernie
MS Excel MVP

"kg82" wrote in message

...



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:F2G2,"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!- Hide quoted text -

- Show quoted text -

Youa re asking about: SUMPRODUCT(--(A2:F2G2))0

This part

A2:F2G2

returns an array of True/False Values

This part

--(A2:F2G2)

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

This part

SUMPRODUCT(--(A2:F2G2))

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:F2G2))

(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






"nlp239" wrote in message
...
Purely out of interest, would you please explain this statement:
=IF(SUMPRODUCT(--(A2:F2G2))0

Please note, I am NOT the OP.
Thanks


On Oct 20, 12:26 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Something like

=IF(SUMPRODUCT(--(A2:F2G2))0,"non compliant", "compliant")

HTH,
Bernie
MS Excel MVP

"kg82" wrote in message

...



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:F2G2,"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!- Hide quoted text -

- Show quoted text -