Multiple lookups and SUM function
 

Hi,

I need to lookup values in two columns and where matches are found, SUM a
numeric in a third column. The below data probably explains it better:-

A B C
Name yes or no Total
Joe yes 7
Adam yes 1
David yes 5
Joe yes 3
Joe no 4
Adam yes 3

D1=Joe
E1=yes

Where column A ='Joe' and column B='yes' then SUM the content of column C.
So in this case it would return '10' (i.e. 7+3).

Many thanks :-)

Multiple lookups and SUM function
 

=SUM((A2:A7=D1)*(B2:B7=E1)*C2:C7)

But press ctrl+Shift+Enter rather than just enter by itself to set the formula

"DevonDilema" wrote:

Hi,

I need to lookup values in two columns and where matches are found, SUM a
numeric in a third column. The below data probably explains it better:-

A B C
Name yes or no Total
Joe yes 7
Adam yes 1
David yes 5
Joe yes 3
Joe no 4
Adam yes 3

D1=Joe
E1=yes

Where column A ='Joe' and column B='yes' then SUM the content of column C.
So in this case it would return '10' (i.e. 7+3).

Many thanks :-)

Multiple lookups and SUM function
 

Try this:

=Sumproduct((A2:A10=D1)*(B2:B10=E1)*C2:C10)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"DevonDilema" wrote in message
...
Hi,

I need to lookup values in two columns and where matches are found, SUM a
numeric in a third column. The below data probably explains it better:-

A B C
Name yes or no Total
Joe yes 7
Adam yes 1
David yes 5
Joe yes 3
Joe no 4
Adam yes 3

D1=Joe
E1=yes

Where column A ='Joe' and column B='yes' then SUM the content of column C.
So in this case it would return '10' (i.e. 7+3).

Many thanks :-)

Multiple lookups and SUM function
 

Hi,
try

=sumproduct(--(D1=A1:A1000),--(E1=B1:B1000),C1:C1000)

if you are using excel 2007 enter

=sumproduct(--(D1=A:A),--(E1=B:B),C:C)

"DevonDilema" wrote:

Hi,

I need to lookup values in two columns and where matches are found, SUM a
numeric in a third column. The below data probably explains it better:-

A B C
Name yes or no Total
Joe yes 7
Adam yes 1
David yes 5
Joe yes 3
Joe no 4
Adam yes 3

D1=Joe
E1=yes

Where column A ='Joe' and column B='yes' then SUM the content of column C.
So in this case it would return '10' (i.e. 7+3).

Many thanks :-)

Multiple lookups and SUM function
 

if you are using excel 2007 enter
=sumproduct(--(D1=A:A),--(E1=B:B),C:C)

If you are using XL2007, I would you use a built-in function call SUMIFS()
rather than use SUMPRODUCT()

=SUMIFS(C:C,A:A,D1,B:B,E1)


"Eduardo" wrote:

Hi,
try

=sumproduct(--(D1=A1:A1000),--(E1=B1:B1000),C1:C1000)

if you are using excel 2007 enter

=sumproduct(--(D1=A:A),--(E1=B:B),C:C)

"DevonDilema" wrote:

Hi,

I need to lookup values in two columns and where matches are found, SUM a
numeric in a third column. The below data probably explains it better:-

A B C
Name yes or no Total
Joe yes 7
Adam yes 1
David yes 5
Joe yes 3
Joe no 4
Adam yes 3

D1=Joe
E1=yes

Where column A ='Joe' and column B='yes' then SUM the content of column C.
So in this case it would return '10' (i.e. 7+3).

Many thanks :-)