# Sumproduct

##### Board Regular
I am using this formula to get the results,

Code:
``=SUMPRODUCT(--(Sheet2!\$M\$2:\$M\$11=Sheet1!\$B\$3),--(Sheet2!\$N\$2:\$N\$11=Sheet1!\$B\$4),Sheet2!\$A\$2:\$A\$11)``

inthe above quoted formula last part "Sheet2!\$A\$2:\$A\$11" is the data for Jan in sheet2, if I need Feb results I need to change it to "Sheet2!\$B\$2:\$B\$11"
Is there a way to make it dynamic by linking this to a cell on sheet1? Example if sheet1 A1 cell contains name of month, by changing that the results should also change?

Thank you

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### mgirvin

##### Well-known Member

If you name the ranges Sheet2!\$A\$2:\$A\$11 and Sheet2!\$B\$2:\$B\$11 “Jan”, and :”Feb”, then you can use the INDIRECT function.

=SUMPRODUCT(--(Sheet2!\$M\$2:\$M\$11=Sheet1!\$B\$3),--(Sheet2!\$N\$2:\$N\$11=Sheet1!\$B\$4),INDIRECT(CellWithName)).

Then you can just change the name in the cell and the formula will update.

Last edited:

##### Board Regular
Thank you, it works

#### mgirvin

##### Well-known Member

You know, I had another idea that would avoid using the volatile INDIRECT function (for big spreadsheets volatility may slow calculations). Try this formula (where Sheet2!\$A\$1:\$B\$1 hold the column names Jan and Feb and MonthCell has either Jan or Feb it it):

=SUMPRODUCT((Sheet2!\$M\$2:\$M\$11=Sheet1!\$B\$3)*(Sheet2!\$N\$2:\$N\$11=Sheet1!\$B\$4)*(Sheet2!\$A\$1:\$B\$1=MonthCell)*Sheet2!\$A\$2:\$B\$11)

However, you do not want to use this Double Negative form of SUMPRODUCT:

=SUMPRODUCT(--(Sheet2!\$M\$2:\$M\$11=Sheet1!\$B\$3),--(Sheet2!\$N\$2:\$N\$11=Sheet1!\$B\$4),--(Sheet2!\$A\$1:\$B\$1=MonthCell),Sheet2!\$A\$2:\$B\$11)

Why? Because the Double Negative will not handle the different sized ranges, but the multiplying will handle them.

##### MrExcel MVP
Assuming that the formula is in Sheet1...
Code:
``````=SUMPRODUCT(
--(Sheet2!\$M\$2:\$M\$11=\$B\$3),
--(Sheet2!\$N\$2:\$N\$11=\$B\$4),
INDEX(Sheet2!\$A\$2:\$M\$11,0,E2))``````
where E2 houses 1 for Jan, 2 for Feb, etc.

#### mgirvin

##### Well-known Member

I love it!

Thanks for reminding me of the 0 for the row_num in INDIRECT to return a column of values!

Tell me if I have this right:

1) This formula is faster calculating and shorter to type:

=SUMPRODUCT(--(Sheet2!\$M\$2:\$M\$11=\$B\$3),--(Sheet2!\$N\$2:\$N\$11=\$B\$4),INDEX(Sheet2!\$A\$2:\$M\$11,0,E2))

Than this formula:

=SUMPRODUCT((Sheet2!\$M\$2:\$M\$11=Sheet1!\$B\$3)*(Sheet2!\$N\$2:\$N\$11=Sheet1!\$B\$4)*(Sheet2!\$A\$1:\$B\$1=MonthCell)*Sheet2!\$A\$2:\$B\$11)

2) The formula that I first proposed using the INDIRECT function requires recalculation every time the sheet recalculates, even if data has not changed. This is why the first formula I proposed is generally not the best option.

##### MrExcel MVP

I love it!

Thanks for reminding me of the 0 for the row_num in INDIRECT to return a column of values!

Tell me if I have this right:

1) This formula is faster calculating and shorter to type:

=SUMPRODUCT(--(Sheet2!\$M\$2:\$M\$11=\$B\$3),--(Sheet2!\$N\$2:\$N\$11=\$B\$4),INDEX(Sheet2!\$A\$2:\$M\$11,0,E2))

Than this formula:

=SUMPRODUCT((Sheet2!\$M\$2:\$M\$11=Sheet1!\$B\$3)*(Sheet2!\$N\$2:\$N\$11=Sheet1!\$B\$4)*(Sheet2!\$A\$1:\$B\$1=MonthCell)*Sheet2!\$A\$2:\$B\$11)

Yes.

2) The formula that I first proposed using the INDIRECT function requires recalculation every time the sheet recalculates, even if data has not changed. This is why the first formula I proposed is generally not the best option.

Yes, INDIRECT is volatile, so it tends to prolonge the recalcs.

#### mgirvin

##### Well-known Member

Then I assume if we used a SUMPRODUCT formula and the OFFSET function to find the column of values, that this would also be like INDIRECT and take longer on recalc.

Is this right?

##### MrExcel MVP

Then I assume if we used a SUMPRODUCT formula and the OFFSET function to find the column of values, that this would also be like INDIRECT and take longer on recalc.

Is this right?

Yes, that's right.

#### mgirvin

##### Well-known Member

Thank you for confirming some really good concepts that I can put more firmly into my Excel Tool Box:

1) Try to avoid volatile functions such as INDIRECT and OFFSET (I use these a lot).
2) Double negative to convert TRUEs and FALSEs to 1s and 0s is faster than multiplying
3) 0 in row or column number for INDIRECT can yield a whole column or row, respectively, in place of using INDIRECT and OFFSET functions.

Replies
20
Views
462
Replies
30
Views
503
Replies
3
Views
394
Replies
5
Views
228
Replies
14
Views
228

### Forum statistics

1,195,849
Messages
6,011,957
Members
441,657
Latest member
Diupsy ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back