I would like the average of Column B based on two criteria. That it happened last year and a text criteria from another column.

CodePudding user response：

The SUMPRODUCT() function provides some really useful approaches to problems like this.

• In your sample data, the formula =YEAR(Table1[Date])=2023 should return an array {FALSE,FALSE,TRUE,TRUE,FALSE,TRUE}.

• In your sample data, the formula =Table1[Text]="Up" should return an array {TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}.

SUMPRODUCT() allows us to do some interesting things with those:

If I apply a math operation to those arrays Excel automatically converts them to binary; {0,0,1,1,0,1} and {1,0,0,1,0,0} respectively. That math function can be doing something like multiplying. Or if I want to use them as-is in a function I can just use "--" to force a math operation, that makes them negative and back to positive. In our example we'll be multiplying the arrays so Excel will take care of it for us.

I can do a binary AND operation on the two arrays by using multiplication. Thus: = (YEAR(Table1[Date])=2023) * (Table1[Text]="Up") is actually {0,0,1,1,0,1} * {1,0,0,1,0,0} which in turn equals {0,0,0,1,0,0}. And the 1's in this result array represent the rows that meet both criteria.

=SUMPRODUCT((YEAR(Table1[Date])=2023) * (Table1[Text]="Up")) will equal the count of rows that met both criteria. Which is only 1 in your example.

=SUMPRODUCT((YEAR(Table1[Date])=2023) * (Table1[Text]="Up" * Table1[Unit])) is going to sum the result of array multiplication of {0,0,0,1,0,0} * {4,4,5,5,4,4}. In your sample data that results in 5.

So the formula =SUMPRODUCT((YEAR(Table1[Date])=2023) * (Table1[Text]="Up") * Table1[Unit]) / SUMPRODUCT((YEAR(Table1[Date])=2023) * (Table1[Text]="Up")) actually is "sum of rows that matched" divided by the "count of rows that matched".

Notice that a conditional array like Table1[Text]="Up" MUST be wrapped in its own parenthesis before it can be added (OR function) or multiplied (AND function) with another array.

You may want to wrap that entire formula in an IFERROR() function so you can display a friendlier message when the count is zero. For instance:

=IFERROR(SUMPRODUCT((YEAR(Table1[Date])=2023) * (Table1[Text]="Up") * Table1[Unit]) / SUMPRODUCT((YEAR(Table1[Date])=2023) * (Table1[Text]="Up")),"None")

You will want to fully debug the formula before nesting it in IFERROR() because the IFERROR() function will conceal other errors than just an occasional divide by zero.

This will all seem very cumbersome the first few times you use this approach but if you encounter these kinds of criteria problems often in excel, I promise that taking the time to understand SUMPRODUCT() on logical arrays will pay long-term dividends. Once understood it gives you a robust capability to use SUM, COUNT, and AVERAGE given multiple criteria, that can be any mix of AND and OR criteria.