averageif statement with multiple conditions

[bost78]

I have a column of numbers that I want to get an average of only if the number is greater than zero AND only if it corresponds to a specific name in another column.

 

[MrKowz]

If you use Excel2007, check out the AverageIF formula.

If you don't use Excel2007, you might want to check out the use of a SUMPRODUCT formula paired up with a COUNTA formula.

For example:

=SUMPRODUCT(--(YourFirstRange>0),--(YourSecondRange="Name"))/COUNTA(YourFirstRange)

Both ranges MUST BE OF IDENTICAL DIMENSIONS

 

[barry houdini]

Hello bost78, welcome to MrExcel

Which version of Excel are you using? If you have Excel 2007 or later you can use AVERAGEIFS function (with an "S" on the end), e.g.

=AVERAGEIFS(A1:A100,A1:A100,">0",B1:B100,"John")

or in earlier versions you can use this "array formula"

=AVERAGE(IF(A1:A100>0,IF(B1:B100="John",A1:A100)))

confirmed with CTRL+SHIFT+ENTER

 

[bost78]

i use excel 2007, but cannot determine the formula with the multiple conditions. I can do it with only one of the conditions, but when I add another condition I get an error.

 

[barry houdini]

see my previous reply - you need AVERAGEIFS

 

[bost78]

Got it, it works! Thanks, i've been trying to get this for an hour!!

 

[bost78]

I have another question.

I've used the counta function before, but for this i only want to get a count of cells that are populated if they match a certain criteria in another column. Can you use the counta function for that?

 

[barry houdini]

Try using COUNTIFS

This formula will count populated cells in column A where col B is "x"

=COUNTIFS(A:A,"<>",B:B,"x")