Searching

[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....

 

[Jacob Skaria]

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

 

[Per Jessen]

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]

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]

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

 

[Per Jessen]

Try this formula:

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

Regards,
Per

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Eric]

I am not using this as a sum product. I finally got the formula to look
right but now the error is #ref?

 

[Eric]

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

 

[Eric]

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]

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