Formula Help - average of the sum of cells in a row if another row in the column meets criteria

Tara Factor

Hi, I was hoping someone could help me write a formula? I am trying to write a formula in the cell highlighted in yellow that takes an average of the sum of the cells highlighted in green if the country in row 3 in the same column is set to CANADA.

Is there a way to combine a (INDEX) statement with an AVERAGE IF formula?

jmyzk_cloudsmart_jp

Hi @Tara Factor

Assuming there will be more columns like the Brentwood Cost/per sq ft" and the 300 Main Cost / sq ft," I added a helper row, 0 Cost Type.

Using the Cost Type and Country information, we can get the average with the following formula;

=AVG(COLLECT(Brentwood@row:[500 Park / sq ft]@row, Brentwood$1:[500 Park / sq ft]$1, "per sq ft", Brentwood$4:[500 Park / sq ft]$4, "CANADA"))

We use the COLLECT function as we do not have the AVGIFS function. Please note that this is a cell formula as we reference ranges like "Brentwood$1:[500 Park / sq ft]$1."

The darker shaded area is the one I added to your sample sheet.

https://app.smartsheet.com/b/publish?EQBCT=06ce9fcbb1a74d9d9233e7fb7123971c (Link to publised demo sheet)