Logical function with multiple cells

  • Thread starter Thread starter Demosthenes
  • Start date Start date
D

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?
 
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!
 
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.
 
great! thanks again.

T. Valko said:
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





.
 
Back
Top