Sumproduct

[RPrasad]

I am using this formula to get the results,

=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

 

[mgirvin]

Dear RPrasad,

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.

 

[RPrasad]

Thank you, it works

 

[mgirvin]

Dear RPrasad,

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.

 

[Aladin Akyurek]

ADVERTISEMENT

Assuming that the formula is in Sheet1...

=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]

Dear Aladin,

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.

Thanks for your mastery, Aladin!

 

[Aladin Akyurek]

ADVERTISEMENT

Dear Aladin,

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]

Dear Aladin,

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?

 

[Aladin Akyurek]

Dear Aladin,

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]

Dear Aladin,

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.