1
votes
       A        B     C     D  
1  Department  Jan   Feb   Mar  
2  Marketing   100   200   300  
3  R&D         150   250   350  
4  Marketing   300   400   500

I have a sample file like the above table.

The SUMPRODUCT formula below only sums the specific rows and column, which is row 2 to 20 and column B to C for summing

=SUMPRODUCT((A2:A20="Marketing")*(B2:C20))

Actual formula (from comments):

=SUMPRODUCT(INDEX(BW!$A$3:$AE$3,MATCH(C$35,BW!$1:$1,0)+MATCH("JAN 2016",BW!$F$2:$R$2,0)-1):INDEX(BW!$A$108:$AE$108,MATCH(C$35,BW!$1:$1,0)+MATCH("A‌​UG 2016",BW!$F$2:$R$2,0)-1)*(BW!$B$3:$B$108=$E$1))

My questions, is there a way to make SUMPRODUCT dynamic, in a sense, it knows the first row to take will be 2nd and ends at 20.

As for the dynamic column to sum, it'll depend on the criteria. i.e. if the criteria is Feb and Mar, it'll take column C2:D20...again, the row is dynamic as well.

3
Where is the formula supposed to get Feb and Mar from? Where is it currently getting Jan and Feb from?user4039065
@Jeeped Currently, this is my formula...not as the above. =SUMPRODUCT(INDEX(BW!$A$3:$AE$3,MATCH(C$35,BW!$1:$1,0)+MATCH("JAN 2016",BW!$F$2:$R$2,0)-1):INDEX(BW!$A$108:$AE$108,MATCH(C$35,BW!$1:$1,0)+MATCH("AUG 2016",BW!$F$2:$R$2,0)-1)*(BW!$B$3:$B$108=$E$1))David.L
It looks like you actually typed "A‌​UG 2016" into the worksheet and didn't use a proper date formatted as mmm yyyy.user4039065

3 Answers

2
votes

If you turn your range into a table called Table1 then the following should work:

=SUMPRODUCT((Table1[Department]="Marketing")*(INDIRECT("Table1[[Jan]:["&VLOOKUP("*",Table1[#Headers],COLUMNS(Table1),FALSE)&"]]")))
1
votes

To make the SUMPRODUCT dynamic we can use INDEX/MATCH to find the extents of the data.

Then all that is need is to put the months wanted listed in one cell, I used I7

=SUMPRODUCT((ISNUMBER(SEARCH($B$1:INDEX(1:1,MATCH("ZZZ",1:1)),I7)))*($A$2:INDEX(A:A,MATCH("ZZZ",A:A))="Marketing")*B2:INDEX(A:DD,MATCH("ZZZ",A:A),MATCH("ZZZ",1:1)))

The INDEX(A:A,MATCH("ZZZ",A:A)) will find the last cell with text in Column A, and use that to set the extent of the dataset.

For the Last column we use INDEX(1:1,MATCH("ZZZ",1:1))

enter image description here

1
votes

If you are not going to work with a ListObject (aka Structured) table, then a dynamic named range would certainly help.

  1. Go to Formulas, Defined Names, Name Manager and when the Name Manager dialog opens, click New.
  2. Give your defined name range a unique name; I've chosen Departments.
  3. Leave the Scope as workbook and use the following formula for the Refers to:
     
     =BW!$A$1:INDEX(BW!$A:$M, MATCH("zzz",BW!$A:$A ), MATCH(1E+99,BW!$1:$1 ))
     
    Columns A:M will cover a Department column and Jan through Dec. Note that Jan 2016-Dec 2016 are real dates (e.g. 1/1/2016 to 12/1/2016) formatted as mmm yyyy.
  4. You should end up with the following dynamic named range:

departments

Now you can 'shape' the sections of the defined range using INDEX/MATCH function pairs.

=SUMPRODUCT((INDEX(Departments, 0, 1)=Q3)*
            (INDEX(Departments, 0, MATCH(R3, INDEX(Departments, 1, 0), 0)):
             INDEX(Departments, 0, MATCH(S3, INDEX(Departments, 1, 0), 0))))

departments_FORMULA