Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT that bring Text | Excel Worksheet Functions | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
using sumproduct in a range of text fields? | Excel Worksheet Functions |