Combine Sumproduct Formula

[velu130486]

Dear Seniors,

I am using Sumproduct function with name ranges for my worksheet calculations. Now I would like to seek your help to combine the following formula into a simpler version since the conditions are almost same.

=SUMPRODUCT(CSTSAct153*(WBS=$B10)*(CostRate)*(Billable<=4)*(TYPE="S")) + SUMPRODUCT((ManMonthF*153)*(WBS=$B10)*(CostRate)*(Billable<=4)*(TYPE="S"))

I understood from my friends, excel will take long time if we put long formulas like this...is it true?

Thanks and Regards
R. Vadivelan

 

[Special-K99]

Can't we reduce that to one SUMPRODUCT

=SUMPRODUCT((CSTSAct153*(WBS=$B10)*(CostRate)*(Billable<=4)*(TYPE="S"))) + ((ManMonthF*153)*(WBS=$B10)*(CostRate)*(Billable<=4)*(TYPE="S")))

I'm wondering if we can reduce that evene further since this

(WBS=$B10)*(CostRate)*(Billable<=4)*(TYPE="S")

occurs in both halves of the formula...

 

[Special-K99]

Looking at your ManMonthF*153
should that be CSTSAct153 or CSTSACt*153?

Reducing further...

Try
=SUMPRODUCT((CSTSAct*153+ManMonthF*153)*(WBS=$B10)*(CostRate)*(Billable<=4)*(TYPE="S"))

 

[velu130486]

Hi,
Reply
My formula is correct....It should be CSTSAct153...since here the Values are no need to be multiplied by 153

Whereus it should be multiplied in ManMonthF.

Thanks and Regards
R. Vadivelan

 

[Special-K99]

Does this work?

=SUMPRODUCT((CSTSAct153+ManMonthF*153)*(WBS=$B10)*(CostRate)*(Billable<=4)*(TYPE="S"))

Try this and your original formula, see if you get the same results.

 

[velu130486]

Hi,

Its not working I am getting #N/A Error..Yes my first formula works fine...However its quite long i need to 2 more sets like that...So I need your help for shorter version.

Thanks and Regards
R. Vadivelan

 

[velu130486]

Dear Seniors,

I had tried your methodology, but it gives me a wrong result. Could you please advice me for any alternate solutions, since I need to do the sum product for 6 arrays.

 

[Aladin Akyurek]

Dear Seniors,

I had tried your methodology, but it gives me a wrong result. Could you please advice me for any alternate solutions, since I need to do the sum product for 6 arrays.

Should be something like:

=SUMPRODUCT((CSTSAct153+ManMonthF*153)*(WBS=$B10)*(CostRate)*(Billable<=4)*(TYPE="S"))

 

[velu130486]

Dear Aladin,

I had tried your formula, but gives me the wrong result.

I had split the formula and calculated the result, but I am getting wrong result in this area

=SUMPRODUCT(ManMonthTF) = 2643
=SUMPRODUCT(MMAfter15Jan) = 25
=SUMPRODUCT(ManMonthTF+MMAfter15Jan) = 2987. It should be 2667

Thanks in advance for alternate solutions.

 

[Aladin Akyurek]

Dear Aladin,

I had tried your formula, but gives me the wrong result.

I had split the formula and calculated the result, but I am getting wrong result in this area

=SUMPRODUCT(ManMonthTF) = 2643
=SUMPRODUCT(MMAfter15Jan) = 25
=SUMPRODUCT(ManMonthTF+MMAfter15Jan) = 2987. It should be 2667

Thanks in advance for alternate solutions.

Try to post directly to the forum a scaled-down sample along with the expected result(s). A scaled-down sample can be posted here using one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl.