-1

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:

Formula/sheet overview

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

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
VHes
  • 15
  • 5
  • You are getting `DIV/0` because your AVERAGEIFS function requires that ALL criteria be met on each entry. Since your criteria range cannot be equal to 2 products at the same time, it will fail. Do you have the `SEQUENCE` function? – Ron Rosenfeld Dec 11 '20 at 14:14
  • I see, that is quite logical actually. No I do not seem to have the sequence function. What does it do and where do I obtain it (other version of excel I assume or an add-in?) Thanks for your reply Ron! – VHes Dec 12 '20 at 11:58
  • `SEQUENCE` is available in Excel in O365. Online Excel HELP explains its function better than I can in a comment. – Ron Rosenfeld Dec 13 '20 at 02:00

1 Answers1

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:

enter image description here

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)

enter image description here

and our Dates:

enter image description here

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

will return an array of values

enter image description here

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)

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60