Nested SumIF - variable sumrange

[Conchubhair]

I want to use a fairly basic sumif formula:

The range is defined: Col A ("Political Party Name")

The criteria is defined ("The header of Column F - a political party")

However the sumrange needs to vary with the data in column B, which contains a list of column numbers.

EG

Cols C, D, E contain vote numbers relating to different counts, and the number in Col B indicates which Column to sum if the criteria are satisfied.


Can't get it to work - does Excel permit nested formulae in SUMIF - presume it doesn't if I have to use an array for multivariate sumifs? Tried bastardising using combinations of LEFT(ADDRESS(),2), but that only returns the col letter, rather than the column range, and if I concatenate it with a "Col letter"&":"&"col letter", it returns a text string rather than a range reference which Excel recognises.

Proposals for any array, or simpler workarounds greatfully recd!

 

[jwst87]

Hi,

Have you tried playing around with the offset function?

i.e. =SUMIF(A:A,L1,OFFSET(B:B,0,B2-1))

With L1 being your criteria and B2 containing the column number of the data you want to sum.

Jon

 

[Conchubhair]

Like a charm - many thanks!