** The AVERAGEIF function in Excel is designed to calculate the average of cells that meet a single condition, but it works only with a single range. If you want to average values across multiple columns based on specific criteria, you need to use a combination of functions such as AVERAGE, IF, and ARRAYFORMULAS (or AVERAGEIFS with clever structuring).
Here's how to do it using an array formula:
=AVERAGE(IF((A2:A10="Yes") + (B2:B10="Yes"), C2:C10))
This formula checks if either column A or B contains "Yes", and if so, it includes the corresponding value from column C in the average. Press Ctrl+Shift+Enter (for older Excel versions) to enter it as an array formula.
Alternatively, if you want to average values in multiple columns, like averaging columns D, E, and F only when a condition in column A is met, use:
=AVERAGE(IF(A2:A10="Yes", (D2:D10 + E2:E10 + F2:F10)/3))
Again, array entry is needed in Excel versions before dynamic arrays. For dynamic Excel (365/2021), these auto-calculate.
Using helper columns or LET and LAMBDA can further optimize complex calculations. **
Put Comment for quarry