Sum Product Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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)))
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 $ $
Click to expand...

--

[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.
 
Back
Top