# Averageifs with an or statement

1. ## 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

2. ## Re: Averageifs with an or statement

=average(if((OfferedNSC!\$K:\$K=\$C22)*(OfferedNSC!\$R:\$R=L\$12)*(OfferedNSC!\$L:\$L={"m","t"}),OfferedNSC!\$Q:\$Q)) array entered should work

3. ## Re: Averageifs with an or statement

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.

4. ## Re: Averageifs with an or statement

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

5. ## Re: Averageifs with an or statement

Thank you all!! Greatly appreciated. Looks like it is working now thank you

6. ## Re: Averageifs with an or statement

You're welcome. We appreciate the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)