Logical function with multiple cells

[Demosthenes]

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?

 

[T. Valko]

Try this...

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

 

[Demosthenes]

Excellent! Exactly what I wanted. Thanks Biff!

Try this...

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

--
Biff
Microsoft Excel MVP





.

 

[Demosthenes]

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]

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.

 

[Demosthenes]

great! thanks again.

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





.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

great! thanks again.