# help with finding and adding lists up

#### JASONW10

##### Board Regular
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
ABCDEFGHI
1DatePart#inspectionpart #Date11/24/05
211/24/05lm4564902a145
311/24/05lm4564902a110lm4564902a155
411/24/05lm37459022215lm37459022215
511/24/08lm2354912a120lm2354912a10
Sheet3

wow thxs alot this works great

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

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)

Replies
4
Views
319
Replies
0
Views
188
Replies
0
Views
160
Replies
9
Views
156
Replies
5
Views
186

1,216,175
Messages
6,129,305
Members
449,499
Latest member
HockeyBoi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back