Need help with a multiple criteria formula

[Sam]

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?

 

[Teethless mama]

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

 

[Sam]

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.

 

[Toppers]

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!D230000=C2),Detail!E2:E30000)

 

[Sam]

It worked....thanks.

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

Again, many thanks.

 

[Toppers]

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

 

[butterfly]

I have a workbook with multiple worksheets contaning data. The data is
arranged in columns with the first column containg a unique part number and
follwing columns data related to that part number. I would like to set up the
first worksheet where you enter the part number you are looking for and excel
takes you to the worksheet where that part number is found, it will only
appear in one worksheet. Would it be possible to take you to the cell
containg the part number?

 

[Pete_UK]

You could use a HYPERLINK function in a separate cell which also
includes nested MATCH functions, so that you type your ID number and
then click on the next cell to jump there.

However, it will get a bit complicated depending on how many you mean
by "multiple" sheets.

If you want further advice on a possible formula please provide
further details, like sheet names, column ranges used etc.

Hope this helps.

Pete