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

 

[Aladin Akyurek]

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.

Yes to all, except in 3, replace the first occurrence of INDIRECT with INDEX...

 

[mgirvin]

Dear Aladin,

Well, ok… now can you tell me more? Can you give me an example of when it would be beneficial to replace the first occurrence of INDIRECT with INDEX, but then use INDIRECT subsequently?

 

[Aladin Akyurek]

Dear Aladin,

Well, ok… now can you tell me more? Can you give me an example of when it would be beneficial to replace the first occurrence of INDIRECT with INDEX, but then use INDIRECT subsequently?

I thought we had an example of having INDEX instead of INDIRECT in this very thread.

INDIRECT and OFFSET are also very useful function when used appropriately/judiciously. There is no substitution for INDIRECT is possible in a SumIf formula across multiple sheets:

http://www.mrexcel.com/forum/showthread.php?t=119020

 

[mgirvin]

Dear Aladin,

I misunderstood what you said when you said “replace the first occurrence of INDIRECT with INDEX”. I understand now that you meant the first formula that I posted.

I originally thought that you mean there was some special circumstance when you would have two INDIRECT functions in a formula and then you would replace the first INDIRECT, but not the second.

I will look at this thread that you posted.

 

[mgirvin]

Dear Aladin,

Out of confusion comes enlightenment...

I see what you mean. Yes, I meant to say this:

3) 0 in row or column number for INDEX can yield a whole column or row, respectively, in place of using INDIRECT and OFFSET functions.