Sumifs formula where sum range changes depending on Cell Value

2
votes

I am trying to do a SUMIFS formula where the column I am summing (the sum_range) will depend on the column heading matching a cell value in another sheet.

So for example, cell A2 in my sheet might say 'Actual Sales' and the formula will sum column X in my data sheet (because cell X1 = "Actual Sales"). But if I change cell A2 to 'Budgeted Sales' I want the formula to sum from column AG (so cell AG1 = "Budgeted Sales").

The other criteria in the SUMIFS formula will not change.

I managed to use an Index & Match formula when doing this on a SUMIF formula but it does not seem to work on the SUMIFS formula.

The basic formula would be as follows:

=SUMIFS(DATA!X:X,DATA!F:F,B26,DATA!D:D,C10,DATA!E:E,DATA!C11)

The formula above is summing column X (Actual Sales) but I want that range to be dependent on what I enter in a particular cell.

Any ideas?

2
excel-formula
you should go for pivot tables. try it – Marcel

2 Answers

2
votes

You can just INDIRECT to specify a range dynamically based on a string input, such as SUM(INDIRECT("DATA!D:D")) which sums all values in column D. Combine this with an IF in order to switch the range used based on a certain condition. You can use this with any formula, including SUMIF or SUMIFS.

For example: INDIRECT Example In this example, there is a SumColumn that contains the number of the SumColumn that I wish to sum. Valid values are 1 and 2.

SumColumn1 and SumColumn2 simply contain values (no formulas).

Cell B2 contains the formula that uses INDIRECT with SUMIFS to sum values based on a condition (all values must be >1). The part that defines the range is:

INDIRECT(IF(DATA!$A$2=1,"DATA!C:C","DATA!D:D"))

Basically, if the data in cell A2 is equal to 1, then use a range of DATA!C:C; otherwise use DATA!D:D.

Now just wrap this in the regular SUMIF (or SUMIFSin your case) to get the result you want.

1
votes

You could try this assuming for demonstration purposes data are in same sheet in columns A and B:-

=SUM(A2:B4*(A1:B1=$C$1))

entered as an array formula in C2.

enter image description here

If you had non-numeric cells in your range of data it wouldn't work, but you could try

=SUM(IF(ISNUMBER(A2:B4),A2:B4,0)*(A1:B1=$C$1))

Here's another method that only selects the column you want (non-array formula)

=SUM(INDIRECT(ADDRESS(2,MATCH($C$1,$A$1:$B$1,0))&":"&ADDRESS(4,MATCH($C$1,$A$1:$B$1,0))))

but you have to put the range of rows (in this case 2 & 4) that you want.

=SUM(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH($C$1,$A$1:$B$1,0)),"$1","")&":"&SUBSTITUTE(ADDRESS(1,MATCH($C$1,$A$1:$B$1,0)),"$1","")))

is better because it sums the whole column.

However the standard method is to use INDEX to return a whole column:-

=SUM(INDEX(A:B,,MATCH($C$1,$A1:$B1)))