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("AUG 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.
=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"AUG 2016"
into the worksheet and didn't use a proper date formatted asmmm yyyy
. – user4039065