Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex SUMIF | Excel Discussion (Misc queries) | |||
Complex SUMIF question | Excel Worksheet Functions | |||
How to use complex criteria in SUMIF() | Excel Worksheet Functions | |||
Can I use more complex logical expression for sumif as creteria? | Excel Worksheet Functions | |||
SumIf formula with complex criteria | New Users to Excel |