=SUMPRODUCT((Sheet1!B1:B3="ABC")*(Sheet1!C1:C3="In surance")*Sheet1!D1:D3)

You can substitute the "text" for a cell that the criteria is entered into.

"Anna Wood" wrote in message
...
Not quite sure how to write the formula to return value for D1 in new
worksheet. I need to match the values for both B1 and C1.

B C D
1 ABC Insurance $100
2 ABC Taxes $200
3 DEF Insurance $150