I want to average a range if one of my criteria range equals EITHER one of two values.
In the attached worksheet column G values should equal the column F values, but using the "correct" averageifs formula.
I want to average a range if one of my criteria range equals EITHER one of two values.
In the attached worksheet column G values should equal the column F values, but using the "correct" averageifs formula.
I have a feeling this is wayyyyy too simplistic, but if you only have Y/N/Blank...
=AVERAGEIFS(C:C,$A:$A,E1,$B:$B,"<>N")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
It does not seem to be working on my actual project.
The options I have are actually O/TBD/Blank.
This is the actual formula: COUNTIFS($C:$C,$C1,$M:$M,"<>O",$X:$X,">="&X2)
Does the fact that it is countifs and not averageifs change things.
Your latest post seems very different from your inital question?
Do you want the average or the count?
It seems that you have 1 criteria in column M and another 1 in column X
Perhaps you need to upload another sample workbook that more closely matches what you want?
Last edited by FDibbins; 11-25-2015 at 09:52 PM. Reason: fixed typos
Try this in F1:F2.Formula:Please Login or Register to view this content.
Dave
You are right FDIBS. I slothly assumed AVERAGEIFS and COUNTIFS are interchangeable. I actually have 3 criteria based on which I want to use the COUNTIF function, and one of those criteria is EITHER "O" or a blank cell.
Will upload another workbook tomorrow. It's 3:30AM in Bulgaria. EEK!
Flame - I will try out your answer later as well - thanks.
You are welcome. Sleep well.
This would be shorter.Formula:Please Login or Register to view this content.
Good news. Everything worked out as expected. Thanks for the help.
It seems like, for my purposes, COUNTIFS and AVERAGEIFS were interchangeable.
The gap in my knowledge was not knowing "<>", now that's fixed.
In the attached spreadsheet column D ranks the data in column C, based on the specified criteria, of which one is that column B NOT be equal to O.
You are welcome. Glad to hear it worked out. Thank you for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks