Logical function with multiple cells
 

Hi,

I'm having trouble with the syntax for a logical function. Given, in A, B
and C:

1 h f
2 b a
4 c e
2 f
9 e
4 d
6 g
2 a

I want to write a function that sums the numbers in A that line up with the
letters in B that also appear in C, and gives one number for an answer:

13

How can I do that? As near as I can tell, VLOOKUP only considers one cell at
a time, and I can't work out the syntax on SUMIF. Anyone have any ideas? AmI
missing the obvious answer?

Logical function with multiple cells
 

Try this...

=SUMPRODUCT(SUMIF(B2:B9,C2:C4,A2:A9))

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
Hi,

I'm having trouble with the syntax for a logical function. Given, in A, B
and C:

1 h f
2 b a
4 c e
2 f
9 e
4 d
6 g
2 a

I want to write a function that sums the numbers in A that line up with
the
letters in B that also appear in C, and gives one number for an answer:

13

How can I do that? As near as I can tell, VLOOKUP only considers one cell
at
a time, and I can't work out the syntax on SUMIF. Anyone have any ideas?
AmI
missing the obvious answer?

Logical function with multiple cells
 

Excellent! Exactly what I wanted. Thanks Biff!

"T. Valko" wrote:

Try this...

=SUMPRODUCT(SUMIF(B2:B9,C2:C4,A2:A9))

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
Hi,

I'm having trouble with the syntax for a logical function. Given, in A, B
and C:

1 h f
2 b a
4 c e
2 f
9 e
4 d
6 g
2 a

I want to write a function that sums the numbers in A that line up with
the
letters in B that also appear in C, and gives one number for an answer:

13

How can I do that? As near as I can tell, VLOOKUP only considers one cell
at
a time, and I can't work out the syntax on SUMIF. Anyone have any ideas?
AmI
missing the obvious answer?


.

Logical function with multiple cells
 

Okay, I have a further question. How could you find the median of those
cells? I can't figure out a way to do it.

Thanks!

"T. Valko" wrote:

Try this...

=SUMPRODUCT(SUMIF(B2:B9,C2:C4,A2:A9))

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
Hi,

I'm having trouble with the syntax for a logical function. Given, in A, B
and C:

1 h f
2 b a
4 c e
2 f
9 e
4 d
6 g
2 a

I want to write a function that sums the numbers in A that line up with
the
letters in B that also appear in C, and gives one number for an answer:

13

How can I do that? As near as I can tell, VLOOKUP only considers one cell
at
a time, and I can't work out the syntax on SUMIF. Anyone have any ideas?
AmI
missing the obvious answer?


.

Logical function with multiple cells
 

Try this array formula** :

=MEDIAN(IF(ISNUMBER(MATCH(B2:B9,C2:C4,0)),A2:A9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
Okay, I have a further question. How could you find the median of those
cells? I can't figure out a way to do it.

Thanks!

"T. Valko" wrote:

Try this...

=SUMPRODUCT(SUMIF(B2:B9,C2:C4,A2:A9))

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
Hi,

I'm having trouble with the syntax for a logical function. Given, in A,
B
and C:

1 h f
2 b a
4 c e
2 f
9 e
4 d
6 g
2 a

I want to write a function that sums the numbers in A that line up with
the
letters in B that also appear in C, and gives one number for an answer:

13

How can I do that? As near as I can tell, VLOOKUP only considers one
cell
at
a time, and I can't work out the syntax on SUMIF. Anyone have any
ideas?
AmI
missing the obvious answer?


.

Logical function with multiple cells
 

great! thanks again.

"T. Valko" wrote:

Try this array formula** :

=MEDIAN(IF(ISNUMBER(MATCH(B2:B9,C2:C4,0)),A2:A9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
Okay, I have a further question. How could you find the median of those
cells? I can't figure out a way to do it.

Thanks!

"T. Valko" wrote:

Try this...

=SUMPRODUCT(SUMIF(B2:B9,C2:C4,A2:A9))

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
Hi,

I'm having trouble with the syntax for a logical function. Given, in A,
B
and C:

1 h f
2 b a
4 c e
2 f
9 e
4 d
6 g
2 a

I want to write a function that sums the numbers in A that line up with
the
letters in B that also appear in C, and gives one number for an answer:

13

How can I do that? As near as I can tell, VLOOKUP only considers one
cell
at
a time, and I can't work out the syntax on SUMIF. Anyone have any
ideas?
AmI
missing the obvious answer?


.



.

Logical function with multiple cells
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
great! thanks again.

"T. Valko" wrote:

Try this array formula** :

=MEDIAN(IF(ISNUMBER(MATCH(B2:B9,C2:C4,0)),A2:A9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
Okay, I have a further question. How could you find the median of those
cells? I can't figure out a way to do it.

Thanks!

"T. Valko" wrote:

Try this...

=SUMPRODUCT(SUMIF(B2:B9,C2:C4,A2:A9))

--
Biff
Microsoft Excel MVP


"Demosthenes" wrote in message
...
Hi,

I'm having trouble with the syntax for a logical function. Given, in
A,
B
and C:

1 h f
2 b a
4 c e
2 f
9 e
4 d
6 g
2 a

I want to write a function that sums the numbers in A that line up
with
the
letters in B that also appear in C, and gives one number for an
answer:

13

How can I do that? As near as I can tell, VLOOKUP only considers one
cell
at
a time, and I can't work out the syntax on SUMIF. Anyone have any
ideas?
AmI
missing the obvious answer?


.



.