Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a multiple criteria formula
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a multiple criteria formula
=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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a multiple criteria formula
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a multiple criteria formula
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a multiple criteria formula
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a multiple criteria formula
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a multiple criteria formula
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? "Toppers" wrote: 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a multiple criteria formula
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 On Jul 18, 7:07*pm, butterfly wrote: 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? "Toppers" wrote: 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:E3000*0) "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?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct formula (multiple criteria) | Excel Discussion (Misc queries) | |||
Can I use an array formula with multiple criteria in the same row? | Excel Worksheet Functions | |||
multiple criteria formula | Excel Worksheet Functions | |||
How do I set up a formula with multiple criteria? | Excel Worksheet Functions | |||
Countif formula with multiple criteria ie >30 and <60? | Excel Worksheet Functions |