AverageIFS with multiple IF criteria from the same column

Question

I tried looking for solutions here but sadly enough I can't find a similar enough issue related to mine. I have quite a lengthy AVERAGEIFS formula:

=AVERAGEIFS(Hoofdtab!AE2:AE19883;
    Hoofdtab!V2:V19883;"="&'Statistics Query'!F4;
    Hoofdtab!V2:V19883;"="&'Statistics Query'!F5;
    Hoofdtab!V2:V19883;"="&'Statistics Query'!F6;
    Hoofdtab!V2:V19883;"="&'Statistics Query'!F7;
    Hoofdtab!I2:I19883;">="&F8;
    Hoofdtab!I2:I19883;"<="&F9;
    Hoofdtab!X2:X19883;"="&F10)

The following screenshot should provide some more clarity:

There are a lot of "Product" entries in the reference list. I want to make it so that I can filter on two / three/ four (cell F5 / F6 / F7) products at once. Currently I am using the asterisk as an 'everything counts' sign. In the example I am only using one product and this works. However, once I add a second product in cell F5 I get a #DIV/0! error. Does anyone know how I can resolve this issue?

I am using Excel 2019

Kind regards,

VHes

Answer 1

To solve your problem, you need to return an array from which you can return the relevant values. AVERAGEIFS requires that all the conditions be true. But you seem to want one OR any of several conditions to be true, in conjunction with another group of conditions where two of them must always be true.

Here is an example that you can use the principles to apply to your actual problem.

Given the following data:

Assume

• we want to select any of a list which might contain a, b, and/or c
• and we want to include a date range.
• we want to average the relevant values in the value column
• we name the
  • column of letters critRng1
  • the column of dates critRng2
  • the column of values avgRng

We have a range where we include our crit values (named crit1 We have a range with the beginning date of the range, and the ending date (DATE1 and DATE2)

For this exercise, we will assume that

• crit1 (note the empty cell)

and our Dates:

The formula: =1/(critRng1=TRANSPOSE(crit1))*1/(critRng2>=DATE1)*1/(critRng2<=DATE2)*avgRng

will return an array of values

Note that the desired values that we wish to average are present, along with a bunch of #DIV/0! errors.

Since the AVERAGE function will ignore text, we can use IFERROR and AVERAGE to get the answer:

=AVERAGE(IFERROR(1/(critRng1=TRANSPOSE(crit1))*1/(critRng2>=DATE1)*1/(critRng2<=DATE2)*avgRng,""))

or, a bit shorter:

=AVERAGE(IFERROR(1/((critRng1=TRANSPOSE(crit1))*(critRng2>=DATE1)*(critRng2<=DATE2))*avgRng,""))

For the above conditions, the formula will return 4 (average of 5, 4 and 3)