SUMPRODUCT search versus other method

[Serge]

I have this program created with the help of many smart people in the user
group & many thanks to all that help.
I have a bolt list sheet and a summary sheet with "sumproduct" formulas that
retreive from the "bolt list" sheet, quantities of different bolt diameter
and length.
But in order for the summary sheet to work, I must input: diameter & length
in the appropreate cell for a specific grade of bolt.
In other word the, "summary" sheet retreives from the "bolt list" sheet and
I would like the in formation from "bolt list" sheet send to the "summary"
sheet without any input from the user.
Is this request logicaly possible? If so, would someone guide me in
acheiving this method please.
Many Thanks
Serge

 

[Bob Phillips]

Put all the diameters in A2 down, all lengths in B1 across, and put the SP
formula in B2

=SUMPRODUCT(--(Sheet1!$A$1:$A$1000=$A2),--($B$A:$B$1000=b$1))

adjust the formula to match your data, then copy down and across

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Serge]

Hello Bob,
Thanks for your reply. You have help me in the past.
=SUMPRODUCT(('Bolt List'!$D$9:$D$188=A8)*('Bolt List'!$J$9:$J$188=B8)*('Bolt
List'!$E$9:$E$188="A325 Black")*(ISNUMBER('Bolt List'!$G$9:$G$188))
*'Bolt List'!$C$9:$C$188)
The formula above applies to one the the bolt grades I work with.
I have been digesting your reply and I'm a little puzzled.
In columns:
C=quantities, D=diameters, E=grades, J=lentgths
In the summary sheet I have 6 areas corresponding to 3 grades of bolts and
each grade are either galvanized, balck or plated.

The above formula is modified for each area.

Currently as I input information in the "Bolt List" sheet which gives me the
length by input of the amount of grip that the connection requires, once I
get this length I go to the "summary" sheet and input that length and the
diameter then I retreive the quantity from the "bolt List" sheet. If while in
the "Bolt List" sheet in the next row the same diameter, grade & length are
required for a different connection, the quantity in the "summary" sheet
keeps updating and so on.
The user needs to remember when filling out the "Bolt List" sheet that if a
different length for same diameter or same length with different diameter,
the user needs to go to the "Summary" sheet and input tthat information in
the next row and so on.
I wish to eliminate this potential disaster.
Any help is greatly appreciated.
Serge

 

[Bob Phillips]

Serge,

Can you send me a sample spreadsheet, and I will knock up something that
should work for you?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)