I need to how how about doing a averageifs with multiple for a few different names. for instance I can do one averageifs with the following code:
=AVERAGEIFS($H:$H,$C:$C,"04PPS",$H:$H,">0")
but what if I wanted the average to include the values from :08PPS" with the same conditions as well?
What if I wanted the avearge to include "04PPS", "08PPS", AND AEROP all with the same conditions?
image of the sheet:
Capture.JPG
With the data you've shown
Formula:
Please Login or Register to view this content.
would do :-)
But probably situation is more complicated.
If just 3 (may be 4 or 5) types of values in column C are to be incluided, I'd probably go just for:
Formula:Please Login or Register to view this content.
Looks long, but is simple total sum for 3 conditions divided by total count for the same conditions.
For more types more compact formula shall be developed.
Best Regards,
Kaper
Assuming that your data is in A2:A14 (headers in row 1), try this:
=AVERAGE(IF((($C$2:$C$14="04PPS")+($C$2:$C$14="08PPS")+($C$2:$C$14="AEROP"))*($H$2:$H$14>0),$H$2:$H$14)) Ctrl Shift Enter
or
=SUM(SUMIFS($H:$H,$C:$C,{"04PPS","08PPS","AEROP"},$H:$H,">0"))/SUM(COUNTIFS($C:$C,{"04PPS","08PPS","AEROP"},$H:$H,">0"))
Last edited by 63falcondude; 10-30-2017 at 10:02 AM. Reason: Included second formula
awesome, both methods work
thanks a ton
Glad we could help. Thanks for the rep!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Glad to hear it workad and thanks for reputation point too
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks