Sum Product Question
 

I need a formula that will complete the data on a sheet 2 after being entered
on sheet 1. The data to be entered is the amount in columns "Red & Blue" and
the rows are are from a validation list which includes nuts, bolts, screws,
and hammers.

Sheet 1
Red Blue Type
$5.00 $3.00 nuts
$3.00 $7.00 nuts
$5.00 $1.00 bolts
$6.00 hammers
$4.00 bolts
$8.00 screws
______________________________

Sheet 2
Red Blue
nuts
bolts
screws
hammers
TOTAL $ $

Try

=SUMPRODUCT(--(Sheet1!$C$2:$C$100=$A2),INDIRECT(Sheet1!A$2:A$100 ))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"force530" wrote in message
...
I need a formula that will complete the data on a sheet 2 after being
entered
on sheet 1. The data to be entered is the amount in columns "Red & Blue"
and
the rows are are from a validation list which includes nuts, bolts,
screws,
and hammers.

Sheet 1
Red Blue Type
$5.00 $3.00 nuts
$3.00 $7.00 nuts
$5.00 $1.00 bolts
$6.00 hammers
$4.00 bolts
$8.00 screws
______________________________

Sheet 2
Red Blue
nuts
bolts
screws
hammers
TOTAL $ $

Let A1:C7 on Sheet1 house the sample (including labels) to be processed.

Let A1:C6 on Sheet2 house the pcocessing layout you provided, with
labels in B1:C1.

In B2 enter, copy across then down:

=SUMIF(Sheet1!$C$2:$C$7,$A2,INDEX(Sheet1!$A$2:$B$7 ,0,MATCH(B$1,Sheet1!$A$1:$B$1,0)))

force530 wrote:
I need a formula that will complete the data on a sheet 2 after being entered
on sheet 1. The data to be entered is the amount in columns "Red & Blue" and
the rows are are from a validation list which includes nuts, bolts, screws,
and hammers.

Sheet 1
Red Blue Type
$5.00 $3.00 nuts
$3.00 $7.00 nuts
$5.00 $1.00 bolts
$6.00 hammers
$4.00 bolts
$8.00 screws
______________________________

Sheet 2
Red Blue
nuts
bolts
screws
hammers
TOTAL $ $


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.