SUMIF for percentage averages?

**treva26** · 05-04-2010, 08:58 PM

Hi,

I am using SUMIF for some tables that summarize my main data.
This works great for dollar values, but for percentages I need to find the average not the total.

I figure I can get the total, and then count how many there were, and that will give me the average.
Can I use SUMIF to just count how many results are bigger than 0?

My totals formula is:
=SUMIF(FullData!$E:$E,$E95,FullData!$CF:$CF)

I was thinking something like:
=SUMIF(FullData!$E:$E,$E95, if(FullData!$CF:$CF>0,1,0) )

**jeffreybrown** · 05-04-2010, 09:04 PM

It would help to see a sample workbook, but you can use this to count greater than 0.

=COUNTIF($A$1:$A$10,">0")

or with Sumif

=SUMIF($A$1:$A$10,">0")

**treva26** · 05-04-2010, 10:01 PM

Originally Posted by jeffreybrown

It would help to see a sample workbook

Sorry, OK here is an example.

Basically I need to average values from the column, if TWO criteria are met.

**jeffreybrown** · 05-04-2010, 10:17 PM

How about...

=AVERAGE(IF(B5:B22="AREA1",IF(C5:C22>0,C5:C22)))

Enter with Ctrl + Shift + Enter

Sorry I messed that formula up, please use the replaced formula

**treva26** · 05-04-2010, 10:26 PM

Originally Posted by jeffreybrown

How about...

=AVERAGE(IF(B5:B22="AREA1",IF(C5:C22>0,C5:C22)))

Enter with Ctrl + Shift + Enter

Sorry I messed that formula up, please use the replaced formula

Thats it! Thank you

**jeffreybrown** · 05-04-2010, 10:32 PM

You're Welcome

If you are satisfied with the answer provided, please mark the thread as solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save