| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching
I need to do a search based off of three (3) criterias:
#1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
With
E1 Date E2 Job Number E3 Mix Type and amount in ColumnD try the below array formula.. =INDEX($D$2:$D$9,MATCH(1,($A$1:$A$100=E1)*($B$1:$B $100=E2)*($C$1:$C$100=E3),0)) If you are looking at summing the tons =SUMPRODUCT(($A$1:$A$100=E1)*($B$1:$B$100=E2)*($C$ 1:$C$100=E3),$D$1:$D$100) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
It's not working. I have typed it in exactly as you have specified with the
exception of the cells Tons: sheet1A L8 <~~~ search sheet1 AF8:AF900 Date: sheet1A B8 <~~ search sheet1 X8:X900 Project: Sheet1A D8 <~~ search sheet1 Z8:Z900 Mix Type: Sheet1A J8 <~~ Search sheet1 AD8:AD900 The formula is going into cell L8 on sheet1A B8= 5/25 D8=175.3 J8=12.5 L8=500<~~~this is the answer X Z AD AF 8 5/25 180.0 12.5 700 9 5/25 190.0 9.5 800 10 5/26 180.0 12.5 800 11 5/25 175.3 12.5 500 HOpe this help somewhat "Jacob Skaria" wrote: With E1 Date E2 Job Number E3 Mix Type and amount in ColumnD try the below array formula.. =INDEX($D$2:$D$9,MATCH(1,($A$1:$A$100=E1)*($B$1:$B $100=E2)*($C$1:$C$100=E3),0)) If you are looking at summing the tons =SUMPRODUCT(($A$1:$A$100=E1)*($B$1:$B$100=E2)*($C$ 1:$C$100=E3),$D$1:$D$100) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: It's not working. I have typed it in exactly as you have specified with the exception of the cells Tons: sheet1A L8 <~~~ search sheet1 AF8:AF900 Date: sheet1A B8 <~~ search sheet1 X8:X900 Project: Sheet1A D8 <~~ search sheet1 Z8:Z900 Mix Type: Sheet1A J8 <~~ Search sheet1 AD8:AD900 The formula is going into cell L8 on sheet1A B8= 5/25 D8=175.3 J8=12.5 L8=500<~~~this is the answer X Z AD AF 8 5/25 180.0 12.5 700 9 5/25 190.0 9.5 800 10 5/26 180.0 12.5 800 11 5/25 175.3 12.5 500 HOpe this help somewhat "Jacob Skaria" wrote: With E1 Date E2 Job Number E3 Mix Type and amount in ColumnD try the below array formula.. =INDEX($D$2:$D$9,MATCH(1,($A$1:$A$100=E1)*($B$1:$B $100=E2)*($C$1:$C$100=E3),0)) If you are looking at summing the tons =SUMPRODUCT(($A$1:$A$100=E1)*($B$1:$B$100=E2)*($C$ 1:$C$100=E3),$D$1:$D$100) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
If you are using SUMPORODUCT() then it is a normal formula
=SUMPRODUCT((X8:X900=B8)*(Z8:Z900=D8)*(AD8:AD900=J 8),AF8:AF900) Tons: sheet1A L8 <~~~ search sheet1 AF8:AF900 Date: sheet1A B8 <~~ search sheet1 X8:X900 Project: Sheet1A D8 <~~ search sheet1 Z8:Z900 Mix Type: Sheet1A J8 <~~ Search sheet1 AD8:AD900 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: It's not working. I have typed it in exactly as you have specified with the exception of the cells Tons: sheet1A L8 <~~~ search sheet1 AF8:AF900 Date: sheet1A B8 <~~ search sheet1 X8:X900 Project: Sheet1A D8 <~~ search sheet1 Z8:Z900 Mix Type: Sheet1A J8 <~~ Search sheet1 AD8:AD900 The formula is going into cell L8 on sheet1A B8= 5/25 D8=175.3 J8=12.5 L8=500<~~~this is the answer X Z AD AF 8 5/25 180.0 12.5 700 9 5/25 190.0 9.5 800 10 5/26 180.0 12.5 800 11 5/25 175.3 12.5 500 HOpe this help somewhat "Jacob Skaria" wrote: With E1 Date E2 Job Number E3 Mix Type and amount in ColumnD try the below array formula.. =INDEX($D$2:$D$9,MATCH(1,($A$1:$A$100=E1)*($B$1:$B $100=E2)*($C$1:$C$100=E3),0)) If you are looking at summing the tons =SUMPRODUCT(($A$1:$A$100=E1)*($B$1:$B$100=E2)*($C$ 1:$C$100=E3),$D$1:$D$100) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
I am not using this as a sum product. I finally got the formula to look
right but now the error is #ref? "Jacob Skaria" wrote: If you are using SUMPORODUCT() then it is a normal formula =SUMPRODUCT((X8:X900=B8)*(Z8:Z900=D8)*(AD8:AD900=J 8),AF8:AF900) Tons: sheet1A L8 <~~~ search sheet1 AF8:AF900 Date: sheet1A B8 <~~ search sheet1 X8:X900 Project: Sheet1A D8 <~~ search sheet1 Z8:Z900 Mix Type: Sheet1A J8 <~~ Search sheet1 AD8:AD900 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: It's not working. I have typed it in exactly as you have specified with the exception of the cells Tons: sheet1A L8 <~~~ search sheet1 AF8:AF900 Date: sheet1A B8 <~~ search sheet1 X8:X900 Project: Sheet1A D8 <~~ search sheet1 Z8:Z900 Mix Type: Sheet1A J8 <~~ Search sheet1 AD8:AD900 The formula is going into cell L8 on sheet1A B8= 5/25 D8=175.3 J8=12.5 L8=500<~~~this is the answer X Z AD AF 8 5/25 180.0 12.5 700 9 5/25 190.0 9.5 800 10 5/26 180.0 12.5 800 11 5/25 175.3 12.5 500 HOpe this help somewhat "Jacob Skaria" wrote: With E1 Date E2 Job Number E3 Mix Type and amount in ColumnD try the below array formula.. =INDEX($D$2:$D$9,MATCH(1,($A$1:$A$100=E1)*($B$1:$B $100=E2)*($C$1:$C$100=E3),0)) If you are looking at summing the tons =SUMPRODUCT(($A$1:$A$100=E1)*($B$1:$B$100=E2)*($C$ 1:$C$100=E3),$D$1:$D$100) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
After placing the formula and making it an array formula, I am getting an
error #N/A. I worked on it for hours last night and couldn't figure it out. It is probably something small but I can't get it to work. Here is the formula as I have it in the bar....{=INDEX(Sheet1!AF8:AF900,MATCH(1,Sheet1!X8: X900=B8)*(Sheet1!Z8:Z900=D8)*(Sheet1!AD8:AD900=J8) ,0)} sheet1 AF is where the answer is located Sheet1 x is where the date is located sheet1 z is where the contract number is located sheet1 AD is where the mix type is located sheet1A is where B8, D8, and J8 are located I do not want to have a sum product I do have a couple of questions.... 1. I thought the * always ment to multiply a formula 2. What does the 1 (after the word Match) mean? Any help would be appreciated "Jacob Skaria" wrote: If you are using SUMPORODUCT() then it is a normal formula =SUMPRODUCT((X8:X900=B8)*(Z8:Z900=D8)*(AD8:AD900=J 8),AF8:AF900) Tons: sheet1A L8 <~~~ search sheet1 AF8:AF900 Date: sheet1A B8 <~~ search sheet1 X8:X900 Project: Sheet1A D8 <~~ search sheet1 Z8:Z900 Mix Type: Sheet1A J8 <~~ Search sheet1 AD8:AD900 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: It's not working. I have typed it in exactly as you have specified with the exception of the cells Tons: sheet1A L8 <~~~ search sheet1 AF8:AF900 Date: sheet1A B8 <~~ search sheet1 X8:X900 Project: Sheet1A D8 <~~ search sheet1 Z8:Z900 Mix Type: Sheet1A J8 <~~ Search sheet1 AD8:AD900 The formula is going into cell L8 on sheet1A B8= 5/25 D8=175.3 J8=12.5 L8=500<~~~this is the answer X Z AD AF 8 5/25 180.0 12.5 700 9 5/25 190.0 9.5 800 10 5/26 180.0 12.5 800 11 5/25 175.3 12.5 500 HOpe this help somewhat "Jacob Skaria" wrote: With E1 Date E2 Job Number E3 Mix Type and amount in ColumnD try the below array formula.. =INDEX($D$2:$D$9,MATCH(1,($A$1:$A$100=E1)*($B$1:$B $100=E2)*($C$1:$C$100=E3),0)) If you are looking at summing the tons =SUMPRODUCT(($A$1:$A$100=E1)*($B$1:$B$100=E2)*($C$ 1:$C$100=E3),$D$1:$D$100) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
Try this formula:
=SUMPRODUCT(--(X8:X900=B8),--(Y8:Y900=D8),--(Z8:Z900=J8),AA8:AA900) Regards, Per "Eric" skrev i meddelelsen ... It's not working. I have typed it in exactly as you have specified with the exception of the cells Tons: sheet1A L8 <~~~ search sheet1 AF8:AF900 Date: sheet1A B8 <~~ search sheet1 X8:X900 Project: Sheet1A D8 <~~ search sheet1 Z8:Z900 Mix Type: Sheet1A J8 <~~ Search sheet1 AD8:AD900 The formula is going into cell L8 on sheet1A B8= 5/25 D8=175.3 J8=12.5 L8=500<~~~this is the answer X Z AD AF 8 5/25 180.0 12.5 700 9 5/25 190.0 9.5 800 10 5/26 180.0 12.5 800 11 5/25 175.3 12.5 500 HOpe this help somewhat "Jacob Skaria" wrote: With E1 Date E2 Job Number E3 Mix Type and amount in ColumnD try the below array formula.. =INDEX($D$2:$D$9,MATCH(1,($A$1:$A$100=E1)*($B$1:$B $100=E2)*($C$1:$C$100=E3),0)) If you are looking at summing the tons =SUMPRODUCT(($A$1:$A$100=E1)*($B$1:$B$100=E2)*($C$ 1:$C$100=E3),$D$1:$D$100) If this post helps click Yes --------------- Jacob Skaria "Eric" wrote: I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
Hi Eric
We need a bit more information to help you. Some sample data would be great along with a description in words of what you want to do and the expected result. Regards, Per "Eric" skrev i meddelelsen ... I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
After placing the formula and making it an array formula, I am getting an
error #N/A. I worked on it for hours last night and couldn't figure it out. It is probably something small but I can't get it to work. Here is the formula as I have it in the bar....{=INDEX(Sheet1!AF8:AF900,MATCH(1,Sheet1!X8: X900=B8)*(Sheet1!Z8:Z900=D8)*(Sheet1!AD8:AD900=J8) ,0)} sheet1 AF is where the answer is located Sheet1 x is where the date is located sheet1 z is where the contract number is located sheet1 AD is where the mix type is located sheet1A is where B8, D8, and J8 are located I do not want to have a sum product I do have a couple of questions.... 1. I thought the * always ment to multiply a formula 2. What does the 1 (after the word Match) mean? Any help would be appreciated "Per Jessen" wrote: Hi Eric We need a bit more information to help you. Some sample data would be great along with a description in words of what you want to do and the expected result. Regards, Per "Eric" skrev i meddelelsen ... I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching
Hi Eric
I tried your formula, but I can't seem to get it return the correct result. When excel evaluate a statement, it return True or False, which will be turnd to values (1/0) when you multiply theese results (or multiply by 1) The 1 in the Match function is the value to match, but the function does not return 1 or 0 but true or false, so if you replace 1 by true, you will not get #N/A, but the value 700 which is the first match for the date. I tried the sumproduct formula I have posted earlier, and changed the ranges to the correct ones, and it returns the correct result on your sample data: =SUMPRODUCT(--(X8:X900=B8),--(Z8:Z900=D8),--(AD8:AD900=J8),AF8:AF900) Best regards, Per "Eric" skrev i meddelelsen ... After placing the formula and making it an array formula, I am getting an error #N/A. I worked on it for hours last night and couldn't figure it out. It is probably something small but I can't get it to work. Here is the formula as I have it in the bar....{=INDEX(Sheet1!AF8:AF900,MATCH(1,Sheet1!X8: X900=B8)*(Sheet1!Z8:Z900=D8)*(Sheet1!AD8:AD900=J8) ,0)} sheet1 AF is where the answer is located Sheet1 x is where the date is located sheet1 z is where the contract number is located sheet1 AD is where the mix type is located sheet1A is where B8, D8, and J8 are located I do not want to have a sum product I do have a couple of questions.... 1. I thought the * always ment to multiply a formula 2. What does the 1 (after the word Match) mean? Any help would be appreciated "Per Jessen" wrote: Hi Eric We need a bit more information to help you. Some sample data would be great along with a description in words of what you want to do and the expected result. Regards, Per "Eric" skrev i meddelelsen ... I need to do a search based off of three (3) criterias: #1 Date #2 Job Number #3 Mix Type Then the amount of tons should show up Can anyone help please.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Searching | Excel Discussion (Misc queries) | |||
| Program Speed - Arrays Searching vs. Row Searching | Excel Programming | |||
| Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
| VBA searching | Excel Programming | |||
| Searching for "?" | New Users to Excel | |||
Hybrid Mode
