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" <[email protected]> wrote in message
news:[email protected]...
> 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.