In the attached workbook, I would like to create a formula on Sheet 2, Column B, that averages the Sheet RR, Column J, when the Descripton on Sheet 2 matches the Description on Sheet RR AND Sheet RR Column H contains either "New" OR "Transfer. Can you do an AverageIFS based on an OR?Tilley.xlsx
This should help:
http://www.excel-university.com/sumifs-with-or/
It seems you will need an array formula:
http://www.mrexcel.com/forum/excel-q...o-somehow.html
Last edited by mikeTRON; 04-02-2015 at 10:28 AM.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
I'm not aware that AVERAGEIFS can do an OR
Try
=IF(SUMPRODUCT(--(RR!D$1:D$537=A1)*((RR!H$1:H$537="New")+(RR!H$1:H$537="Transfer")))=0,0,SUMPRODUCT((RR!D$1:D$537=A1)*((RR!H$1:H$537="New")+(RR!H$1:H$537="Transfer")))/SUMPRODUCT(--(RR!D$1:D$537=A1)*((RR!H$1:H$537="New")+(RR!H$1:H$537="Transfer")))/SUMPRODUCT(--(RR!D$1:D$537=A1)*((RR!H$1:H$537="New")+(RR!H$1:H$537="Transfer"))))
Blimey! SUMIFs and COUNTIFs would probably be better as suggested
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks