Complex SUMIF/COUNT IF
 

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!!

Complex SUMIF/COUNT IF
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Evan" wrote:
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!!

Complex SUMIF/COUNT IF
 

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" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Evan" wrote:
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!!

Complex SUMIF/COUNT IF
 

"Evan" wrote:
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 befo
=AVERAGE(IF((ISNUMBER(MATCH(A1:A5,{"John";"Jane"}, 0)))*(B1:B5<""),B1:B5))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Complex SUMIF/COUNT IF
 

Thanks Max. Really appreciate it

"Max" wrote:

"Evan" wrote:
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 befo
=AVERAGE(IF((ISNUMBER(MATCH(A1:A5,{"John";"Jane"}, 0)))*(B1:B5<""),B1:B5))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Complex SUMIF/COUNT IF
 

welcome, Evan. The feedback is appreciated.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Evan" wrote in message
...
Thanks Max. Really appreciate it