Sumproduct Text
 

I have a spreadsheet (Sheet1) that consists of 4 Colums:

1) A B C
D
2) Hotel Chain Property Name Check-in Date Discount
3) Hilton San Diego Golf 12/5/2005 $25.00 Off

Then I've created a form on another page (sheet2) to validate discounts.
So If I plug in "Hilton" on Cell A3, "San Diego Golf" on Cell B3, and
"12/5/2005" on Cell C3, My result of course should come out as "$25.00 Off"


I've tried using this formula:

=SUMPRODUCT(--(A3=Sheet1!A3:A38),--(B3=Sheet1!B3:B38),--(C3=Sheet1!C3:C38),Sheet1!D3:D38)

But because Column D is Text it's not reading it.

My Question:

How Can I sumproduct as text

Try this *array* formula in Column D of Sheet2:

=INDEX(Sheet1!D3:D38,MATCH(1,(Sheet1!A3:A38=A3)*(S heet1!B3:B38=B3)*(Sheet1!C
3:C38=C3),0))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"tamato43" wrote in message
...
I have a spreadsheet (Sheet1) that consists of 4 Colums:

1) A B C
D
2) Hotel Chain Property Name Check-in Date Discount
3) Hilton San Diego Golf 12/5/2005 $25.00 Off

Then I've created a form on another page (sheet2) to validate discounts.
So If I plug in "Hilton" on Cell A3, "San Diego Golf" on Cell B3, and
"12/5/2005" on Cell C3, My result of course should come out as "$25.00
Off"


I've tried using this formula:


=SUMPRODUCT(--(A3=Sheet1!A3:A38),--(B3=Sheet1!B3:B38),--(C3=Sheet1!C3:C38),S
heet1!D3:D38)

But because Column D is Text it's not reading it.

My Question:

How Can I sumproduct as text