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:C100)

--

Vasant

"Duane" <Duane@discussions.microsoft.com> wrote in message
news:86CD9F88-F9BD-4739-AADB-996B863A1AB2@microsoft.com...
> 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" <Duane@discussions.microsoft.com> wrote in message
news:86CD9F88-F9BD-4739-AADB-996B863A1AB2@microsoft.com...
>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.