Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumif? lookup?
I have three columns of data. A text name, followed by two columns of
numbers. I want the sum of all the numbers in the third column if the criteria in the first two columns is correct. ie Name Unit Pieces John 901 25 George 902 30 John 901 45 John 903 50 George 902 60 There are more names and units and pieces, but I need to sum all of the pieces in the John Units, and George units, etc. How should I accomplish this with a formula? Duane |
#2
|
|||
|
|||
=SUMPRODUCT((A1:A100="John")*(B1:B100=901)*(C1:C10 0)
-- Vasant "Duane" wrote in message ... I have three columns of data. A text name, followed by two columns of numbers. I want the sum of all the numbers in the third column if the criteria in the first two columns is correct. ie Name Unit Pieces John 901 25 George 902 30 John 901 45 John 903 50 George 902 60 There are more names and units and pieces, but I need to sum all of the pieces in the John Units, and George units, etc. How should I accomplish this with a formula? Duane |
#3
|
|||
|
|||
Hi!
=SUMIF(A2:A100,"John",C2:C100) Biff "Duane" wrote in message ... I have three columns of data. A text name, followed by two columns of numbers. I want the sum of all the numbers in the third column if the criteria in the first two columns is correct. ie Name Unit Pieces John 901 25 George 902 30 John 901 45 John 903 50 George 902 60 There are more names and units and pieces, but I need to sum all of the pieces in the John Units, and George units, etc. How should I accomplish this with a formula? Duane |
#4
|
|||
|
|||
Duane wrote:
I have three columns of data. A text name, followed by two columns of numbers. I want the sum of all the numbers in the third column if the criteria in the first two columns is correct. ie Name Unit Pieces John 901 25 George 902 30 John 901 45 John 903 50 George 902 60 There are more names and units and pieces, but I need to sum all of the pieces in the John Units, and George units, etc. How should I accomplish this with a formula? Duane Create an additional column, say D, by invoking in D2: =A2&"#"&B2 Create in F:G from F2 on a list of distnct names and units by running Advanced Filter on A:B with the Unique records only option checked. Then in H2, invoke: =SUMIF($D$2:$D$6,F2&"#"&G2,$C$2:$C$6) Another option is to build a pivot table from A:C. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Using SumIF formulas with multiple lookup values | Excel Worksheet Functions | |||
Help with SUMIF, INDEX, LOOKUP Please !! | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |