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) )
Last edited by treva26; 05-04-2010 at 10:51 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")
Sorry, OK here is an example.Originally Posted by jeffreybrown
Basically I need to average values from the column, if TWO criteria are met.
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
Last edited by jeffreybrown; 05-04-2010 at 10:23 PM.
Thats it! Thank you
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
You're Welcome
If you are satisfied with the answer provided, please mark the thread as solved.
Will do.
Just one other question, if it doesn't find anything that meets the criteria it returns DIV/0
How can I make it return 0 or "" instead?
What version of Excel are you using?
Not sure if this is the most efficient formula, but...
=IF(COUNTIF(B5:B22,"AREA1")=0,"",AVERAGE(IF(B5:B22="AREA1",IF(C5:C22>0,C5:C22))))
CTRL + SHIFT + ENTER
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks