# Combine Sumproduct Formula

#### velu130486

##### Board Regular
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

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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...

should that be CSTSAct153 or CSTSACt*153?

Reducing further...

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

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.

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

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.

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"))

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.

Last edited:

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:

Replies
3
Views
316
Replies
0
Views
254
Replies
1
Views
344
Replies
1
Views
245
Replies
6
Views
280

### Forum statistics

1,203,234
Messages
6,054,276
Members
444,714
Latest member
excel2782 ### 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