I am looking for a formula that will say "Duplicate" based on two criteria.
Say column B has text and column C has dates. If the text appears more than
once and the dates are six or more months apart then the sum of column A
results.
Expamle:
A B C
$45 hardware 11/14/07
$50 hardware 12/04/07
$80 lights 10/01/07
$90 lights 7/25/08
Only $90 is valid in furmula resulting as "Duplicate"

Thanks for your help in advance.

You can use sumproduct

=IF(SUMPRODUCT(--(B4=B$1:B$100),--(ABS(C4-C$1:C$100)=183))0,"Duplicate","")

183 is greater than 6 months (365/2)

Copy formula down the column. Notice where the $ are located. 100 is the
last row (or greater) of your data.

"vadda" wrote:

I am looking for a formula that will say "Duplicate" based on two criteria.
Say column B has text and column C has dates. If the text appears more than
once and the dates are six or more months apart then the sum of column A
results.
Expamle:
A B C
$45 hardware 11/14/07
$50 hardware 12/04/07
$80 lights 10/01/07
$90 lights 7/25/08
Only $90 is valid in furmula resulting as "Duplicate"

Thanks for your help in advance.

You rock, it works perfectly. This will make my project much faster.

"Joel" wrote:

You can use sumproduct

=IF(SUMPRODUCT(--(B4=B$1:B$100),--(ABS(C4-C$1:C$100)=183))0,"Duplicate","")

183 is greater than 6 months (365/2)

Copy formula down the column. Notice where the $ are located. 100 is the
last row (or greater) of your data.

"vadda" wrote:

I am looking for a formula that will say "Duplicate" based on two criteria.
Say column B has text and column C has dates. If the text appears more than
once and the dates are six or more months apart then the sum of column A
results.
Expamle:
A B C
$45 hardware 11/14/07
$50 hardware 12/04/07
$80 lights 10/01/07
$90 lights 7/25/08
Only $90 is valid in furmula resulting as "Duplicate"

Thanks for your help in advance.