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?

=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?

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?

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?

It worked....thanks.

Out of curiosity, what does -- do in the formula? I have not used this
before.

Again, many thanks.


"Toppers" wrote:

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?

the --(double unary) converts a TRUE/FALSE condition to 1/0 so in your formula

if B3 & C2 match then the result is 1*1*value in E=value in E
if B3 and/or C2 do not match then result will be 1*0*value in E=0 (or
0*1*value in E)

HTH

"Sam" wrote:

It worked....thanks.

Out of curiosity, what does -- do in the formula? I have not used this
before.

Again, many thanks.


"Toppers" wrote:

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?