Looking for a formula that averages the numbers located in the 'Salary' column as long as any of the priority columns in a row contain both 'Car' and 'House' in any order, so that this.
| Name | Priority | Priority | Priority | Salary | Average Salary of Car/Home owners |
|---|---|---|---|---|---|
| John | House | Car | Loans | 40,000 | |
| Ned | House | Groceries | Car | 120,000 | |
| Dom | Family | Car | Going Fast | 450,000 | |
| Thanos | Stones | Balance | House | 9,000,000 | |
| Homer | Donuts | Car | House | 20,000 |
could become
| Name | Priority | Priority | Priority | Salary | Average Salary of Car/Home owners |
|---|---|---|---|---|---|
| John | House | Car | Loans | 40,000 | 60,000 |
| Ned | House | Groceries | Car | 120,000 | |
| Dom | Family | Car | Going Fast | 450,000 | |
| Thanos | Stones | Balance | House | 9,000,000 | |
| Homer | Donuts | Car | House | 20,000 |
The average salary column would be used just for the formula in row 1.
CodePudding user response：
One option could be:
=ArrayFormula(AVERAGEIF(MMULT((B2:D="house") (B2:D="car"),ROW(A1:A3)^0),2,E2:E))
CodePudding user response：
The simplest way I can think of is:
=SUMPRODUCT(REGEXMATCH(B2:B&C2:C&D2:D,"House|Car"),E2:E)