Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 $ $ |
#2
|
|||
|
|||
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 $ $ |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Rows/Columns for Copying Formulas | Excel Discussion (Misc queries) | |||
Percentages | Charts and Charting in Excel | |||
How to set a formula to count the product appear how manytime | Excel Worksheet Functions | |||
Which Function(s) do I use? | Excel Worksheet Functions | |||
Which function(s)? | Excel Worksheet Functions |