averageif statement with multiple conditions

bost78

New Member
Joined
Sep 3, 2010
Messages
4
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Try using COUNTIFS

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

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

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top