Searching

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

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....
 
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
 
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
 
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
 
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
 
Try this formula:

=SUMPRODUCT(--(X8:X900=B8),--(Y8:Y900=D8),--(Z8:Z900=J8),AA8:AA900)

Regards,
Per
 
If you are using SUMPORODUCT() then it is a normal formula

=SUMPRODUCT((X8:X900=B8)*(Z8:Z900=D8)*(AD8:AD900=J8),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
Click to expand...

If this post helps click Yes
 
I am not using this as a sum product. I finally got the formula to look
right but now the error is #ref?
 
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
 
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
 
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
 
Back
Top