I am using a SUMIFS
formula in an excel document, and one part of the formula is an array to check if one value meets a number of criteria. However the data in the array is typed out manually, eg {"Nike", "Amazon"}
, however I would like to use a cell value instead of the exact string, i.e. {$K$3, $K$4}
but excel does not allow that.
The formula I am using at the moment is as follows:
=SUM(SUMIFS(INDIRECT(C$2&"[Allocation per Account]"),INDIRECT(C$2&"[Team]"),$J$3,INDIRECT(C$2&"[Role]"),$B3,INDIRECT(C$2&"[Account]"),{"Nike","Thirdbridge","Amazon","EIB","Smith & Nephew","ARUP"}))
Where by the match of the table range [Account]
is set by typing out each value separately.
I have even tried using INDIRECT
to print the strings, {INDIRECT($K$3), INDIRECT($K$4)}
alas to no avail.
Is there a way to change these values to cell references?