I saw a proposed solution here, but it didn't work for me.
How to use arrayformula to find and get data from only a certain, dynamic set of columns?
=SUM(INDIRECT(ADDRESS(4, MATCH("BB", 3:3, 0), 4)&":"& SUBSTITUTE(ADDRESS(4, MATCH("BB", 3:3, 0), 4), "4", "")))
I am trying to do something similar but it's not working ("Formula parse error")
Differences from the original (above):
- I'm trying to do an average, not a sum.
- The data to compare and average are both on another Sheet.
I want to be able to average a column of data below a header from another Sheet,
if the header on that Sheet ('Vends'!) matches the cell to the left on the current Sheet ('AGG-Vends'!).
Eg. If the header on row 2 of Sheet Vends! matches A2 on Sheet 'AGG-Vends'!,
I want to average all the values below that header (row 3 and so on,
with an indeterminate number of values to be added).
My headers on Vends! Sheet are on row 2, so I modified the row number from which to start searching, to row 3.
=AVERAGE(INDIRECT( ADDRESS(3, MATCH(A2, Vends!A2:2, 0), 4, TRUE, Vends!)&":"& SUBSTITUTE( ADDRESS(3, MATCH(A2, Vends!A2:2, 0), 4, TRUE, Vends!), "3", "")))
My formula is in B2 on "AGG-Vends'! Sheet (highlighted Blue). sample Sheet
(Vends may be added or moved around, etc., so I was attempting to circumvent any Sheet-wide catastrophes of having to remodd formulas to fix)