AVERAGEIFS with multiple criteria on one range

[ctsmith84]

I have an AVERAGEIFS statement that I need to take two different criteria on one range. From the looks of it on Microsoft's site, I should be able to do it. Yet when I try it, I get the good ol' #DIV/0! error.

The statement is as follows (they way I thought it would work, anyway):

=AVERAGEIFS(J:J,F:F,"Inactive",D:D,"Equity",D:D,"Fixed Income")


...It works fine if I only have one of the D column criteria (either one of them), but not with both. Can I not put multiple criteria on the same column range in an AVERAGEIFS? If not, could someone please suggest another way of doing this. I basically need to get the average of the values in the J column of all rows left after those three criteria are met.

Thanks in advance!

[NBVC]

Averageifs works on AND conditions, not OR conditions...

You are asking for average if D:D = Equity or if D:D = "Income"

Try:

=AVERAGE(IF((F1:F100="Inactive")*(D1:D100={"Equity","Fixed Income"},J1:J100))

adjust ranges to suit (avoid whole columns or large ranges) and confirm with CTRL+SHIFT+ENTER not just ENTER

or you can use:

=SUM(SUMIFS(J:J,F:F,"Inactive",D:D,{"Equity","Fixed Income"}))/SUM(COUNTIFS(F:F,"Inactive",D:D,{"Equity","Fixed Income"}))

where you can use whole columns and only confirm with Enter

[ctsmith84]

Thank you NBVC. I will try this first thing in the morning when I get back to the office.

The part about it accepting AND and not OR makes sense, and I was wondering if that may be it.

[ctsmith84]

The AVERAGEIFS solution that you gave worked perfectly!

Thanks for the help!

[tangcla]

Hi NBVC: is it possible, for the above example, to have an average if statement where there are two arrays, for example if D:D = "Equity" or "Income", and if F:F = "Active" or "Inactive"?

[NBVC]

Yes it is. Just add the conditions after the F:F criteria range wiring curly brackets like the D:D range.

[T.T]

Hi, I would need your help with AVERAGEIFS:
I have two criteria and using the following formula though, it's not working, tried the one shared by you as well with little tweaks, no success.
=AVERAGEIFS(J4:J303,F4:F303,"XXXX",F4:F303,"YYYY")
=AVERAGE(IF(F4:F303={"XXXX","YYYY"},J4:J303))

[FDibbins]

Hi, I would need your help with AVERAGEIFS:
I have two criteria and using the following formula though, it's not working, tried the one shared by you as well with little tweaks, no success.
=AVERAGEIFS(J4:J303,F4:F303,"XXXX",F4:F303,"YYYY")
=AVERAGE(IF(F4:F303={"XXXX","YYYY"},J4:J303))

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread