Complex SUMIF/COUNT IF

[Evan]

This forum is great. Thanks to those who have helped me in the past.

I am looking for formula help.

I have a list of "Names" in Cells A1:A5 (e.g. A1=John, A2=Jane, A3=John,
A4=Fred, A5=John).

In Column B, I have a list of numbers in Cells B1:B5 (e.g.
B1=2,B2=2,B3=3,B4=4,B5=5,...).

I would like to set up a formula in Column C where I can take an average of
criteria I set in Column A. For example, suppose I choose "John" and "Jane".
My formula result should be 3 ((2+2+3+5)/4). Is there a formula for this? I
have used the formula below succesfully (note, it's an array formula and I
need it to ignore blank cells), but once multiple arguments are inserted, the
formula breaks down.

=(((SUMIF(a1:a5,"john",b1:b5)))/COUNT(IF(a1:a5="john",b1:b5)))

Help would be great. Thanks again!!

 

[Max]

One way

In C1, array-enter* to confirm the formula:
=AVERAGE(IF(ISNUMBER(MATCH(A1:A5,{"John";"Jane"},0)),B1:B5))
*press CTRL+SHIFT+ENTER

Adapt the ranges to suit

 

[Evan]

Max -- Thanks so much. This works except if there's a blank cell in the
range it won't ignore it. How can I tewak it?

 

[Max]

Max -- Thanks so much. This works except if there's a blank cell in the
range it won't ignore it. How can I tweak it?

Try, array-entered (CSE) as before:
=AVERAGE(IF((ISNUMBER(MATCH(A1:A5,{"John";"Jane"},0)))*(B1:B5<>""),B1:B5))

 

[Evan]

Thanks Max. Really appreciate it

Try, array-entered (CSE) as before:
=AVERAGE(IF((ISNUMBER(MATCH(A1:A5,{"John";"Jane"},0)))*(B1:B5<>""),B1:B5))

 

[Max]

welcome, Evan. The feedback is appreciated.