Unless you have Excel2007, SUMPRODUCT has to have defined ranges (rather than
whole columns) so set upper limit to your expected maximum.

=SUMPRODUCT(--(Detail!B2:B30000=B3),--(Detail!D2:D30000=C2),Detail!E2:E30000)


"Sam" wrote:

I made an error on my formula in the initial email. The references to the
"detail" tab should be for the entire columns, not a specific cell.

How would I modify your formula to search the entire columns (B, C, and E)
on the detail tab?

Thanks.


"Teethless mama" wrote:

=SUMPRODUCT(--(Detail!B2:B100=B3),--(Detail!D2:D100=C2),Detail!E2:E100)


"Sam" wrote:

I am working with a workbook with two worksheets. One worksheet has the
data, which can be refreshed changing number of data rows. The other
worksheet is a summary worksheet.

I am trying to match the data in two columns to bring back the results from
a third column. One of the formulas I have tried:
=if(and(B3=Detail!B2,C2=Detail!D2),Detail!E2,"")

I can get a "sum if" formula to work with one criteria, but I can't seem to
get the formula to work with two.

Example of Data:
Column B Column C Column D
001 123456 $1,234
001 234567 $956
001 XYZ123 $2,190
002 123456 $4,321
002 234567 $1,000
002 XYZ345 $5,321

I do not want to concatenate the columns as the data on the detail tab is
refreshed frequently and the number of rows may change.

Any suggestions?