SUMIFS where the "sum_range" column is unknown

0
votes

I have a pile of data on Sheet1 I have a SUMIFS formula on Sheet2 The columns I am summing are named NETPERD1, NETPERD2, NETPERD3.....NETPERD12

I want to write the SUMIFS so that I can easily change which column I am summing. On Sheet2, in cell $C$4, I will enter NETPERD1 or NETPERD2 etc. and I want my SUMIF to determine which column it should sum. I think I should be able to do this with IndexMatch but I can't get it to work.

Here is my SUMIFS that works. I want to replace NETPERD1 with $C$4

=SUMIFS(Sheet1!NETPERD1,Sheet1!ACCTGRPCOD,Perplas!$A15,Sheet1!AUDTORG,Perplas!C$1,Sheet1!FSCSDSG,Perplas!C$2,Sheet1!FSCSYR,Perplas!C$3)

Data on Sheet1

2
excelexcel-formula
Something like =SUMIFS(INDEX(Sheet1!$A:$Z,,Match(Perplas!C$4,Sheet1!A$1:Z$1,0)),... Change the Z to whatever your rightmost column is. Though what row do your named ranges end at? Are they the entire column? - BigBen
Thank you so much! This worked. - SueC
@BigBen For me that's a total valid answer to be posted - Foxfire And Burns And Burns
@FoxfireAndBurnsAndBurns - agreed, added for posterity :-) - BigBen

2 Answers

2
votes

If your named ranges span the entire column, then for the INDEX/MATCH try:

=SUMIFS(INDEX(Sheet1!$A:$Z,,Match(Perplas!C$4,Sheet1!A$1:Z$1,0)),...

changing the Z to your rightmost column.

1
votes

Try using the indirect function. You can supply indirect with a character string that Excel then reads as part of a cell/range reference.

Working off the code you shared + the fact that you said that you'll put in the name of the range in cell C4:

=SUMIFS(INDIRECT("Sheet1!"&$C$4),Sheet1!ACCTGRPCOD,Perplas!$A15,Sheet1!AUDTORG,Perplas!C$1,Sheet1!FSCSDSG,Perplas!C$2,Sheet1!FSCSYR,Perplas!C$3)

I think that should work? I'm a little confused about how you're able to reference the row name without a named range, so that might cause a wrinkle in this.