help with finding and adding lists up

[JASONW10]

heres what i want to do, i want to make a form that lets you put in a date then a part number and a inspection number and a total parts that are made of that part number

A ................B..............C................D
Date..............Part#.......inspection.....part #
11/24/05........lm4564.....902a1..........45


then i want to have a list of part numbers and inspection#

A.................B
lm4564......902a1
lm3745......90222
lm2354......912a1

now i want another form that you can type in a date and it will go down the hole first form find the dates that matches the date you typed in and then if the part# and the inspection number matches up then it will add all the total parts up for that date

thxs

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--($A$2:$A$5=$H$1),--($B$2:$B$5=G3),--($C$2:$C$5=H3),$D$2:$D$5)

in I3 and drag down.

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Part#	inspection	part #			Date	11/24/05
2	11/24/05	lm4564	902a1	45
3	11/24/05	lm4564	902a1	10			lm4564	902a1	55
4	11/24/05	lm3745	90222	15			lm3745	90222	15
5	11/24/08	lm2354	912a1	20			lm2354	912a1	0
Sheet3

 

[JASONW10]

wow thxs alot this works great

 

[JASONW10]

ok this is what i put in and i get a value error
=sumproduct(--(b7=sheet3!A:A),--(C7=SHEET3!B:B),(D7/15)+1)
THIS GIVE'S ME A VALUE ERROR IN THE CELL
=SUMPRODUCT(--(B7=SHEET3!A1),--(C7=SHEET3!B1),(D7/15)+1)
NOW THIS ONE WORKS BUT I WOULD HAVE TO MAKE 1000 DIFFERENT ONES FOR THIS TO WORK. I NEED IT TO LOOK UP A LIST IN THE FIRST CELL. IF THE FIRST CELL MATCHES THEN IT CHECKS TO SEE IF THE SEC CELL MATCHES IF IT DOES THEN IT LOOKS AT D7/15+1.
WHAT AM I DOING WRONG.(BOTH CELLS ARE TEXT, LAST CELL D7 IS A NUMBER)
THXS

 

[fairwinds]

You cannot use entire column references like A:A, change to e.g.A1:A1000.

All ranges must have equal size.

As I do not understand what you realy want to compute here I do not whant to give a formula but if you change the the formula you posted to

=SUMPRODUCT((B7=Sheet3!A1:A1000)*(C7=Sheet3!B1:B1000)*((D7/15)+1))

it should at least give you a result (But probably the wrong result)