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 |
=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 |
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 |
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. |
All times are GMT +1. The time now is 09:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com