Averageifs with an or statement
Hi
I hope you can help! Just keep going round in circles...
I have an averageifs formula but one of the criteria requires it to look for 2 values in a column. I have used a format i found on line and tried many other formats too but i can't get it to bring back the right result. This is what i have used
=AVERAGEIFS(OfferedNSC!$Q:$Q,OfferedNSC!$K:$K,$C22,OfferedNSC!$R:$R,L$12,OfferedNSC!$L:$L,{"m","t"})
Thanks
Kerry
=average(if((OfferedNSC!$K:$K=$C22)*(OfferedNSC!$R:$R=L$12)*(OfferedNSC!$L:$L={"m","t"}),OfferedNSC!$Q:$Q)) array entered should work
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Try this array formula**:
=AVERAGE(IF(OfferedNSC!$K2:$K20=$C22,IF(OfferedNSC!$R2:$R20=L$12,IF(OfferedNSC!$L2:$L20={"m","t"},OfferedNSC!$Q2:$Q20))))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
You should avoid using entire columns as range references in array formulas.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
You won't be able to use AVERAGEIFS, try SUMIFS/COUNTIFS like this
=SUM(SUMIFS(OfferedNSC!$Q:$Q,OfferedNSC!$K:$K,$C22,OfferedNSC!$R:$R,L$12,OfferedNSC!$L:$L,{"m","t"}))/SUM(COUNTIFS(OfferedNSC!$K:$K,$C22,OfferedNSC!$R:$R,L$12,OfferedNSC!$L:$L,{"m","t"}))
or use an "array formula" like this
=AVERAGE(IF((OfferedNSC!$K:$K=$C22)*(OfferedNSC!$R:$R=L$12)*(OfferedNSC!$L:$L={"m","t"}),OfferedNSC!$Q:$Q))
confirmed with CTRL+SHIFT+ENTER
Audere est facere
Thank you all!! Greatly appreciated. Looks like it is working now thank you
You're welcome. We appreciate the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks