SUMPRODUCT criteria

kala

Board Regular
Joined
Nov 6, 2002
Messages
65
Hi! I'm using SUMPRODUCT at a very basic level but am running into an issue because it won't work when columns are not conseutive. I have many different files and do not want to change the order of the columns because they link to other sheets and really, it would be a pain.

Sample Data:
Col A: B, C, D, E, F
Row 2: 10, 20, 30, 40, 50
Row 3: 100, 200, 300, 400, 500

Current Formula: =SUMPRODUCT(B$2:F$2,B3:F3)
This is the formula I want (skip E): first section: B$2:D$2,F$2 / 2nd section: B$3:D$3,F$3

Any quick fixes or do I need to bit the bullet and modify my columns?

Thank you!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Perhaps you can just subtract the E?

=SUMPRODUCT(B$2:F$2,B3:F3)-SUMPRODUCT(E$2,E3)

Or simply
=SUMPRODUCT(B$2:F$2,B3:F3)-(E$2*E3)
 

kala

Board Regular
Joined
Nov 6, 2002
Messages
65
Appreciate the response. I understand your logic but the end result is not the same and not within a reasonable tolerance. Any other ideas?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
It works for me...

Here's the basic formula
=SUMPRODUCT(B$2:F$2,B3:F3)
That's the same as
=(B2*B3)+(C2*C3)+(D2*D3)+(E2*E3)+(F2*F3)

Right so far?

But what you want is

=(B2*B3)+(C2*C3)+(D2*D3)+(F2*F3)
(skipped the column E)

This works for that scenario.

=SUMPRODUCT(B2:F2,B3:F3)-SUMPRODUCT(E2,E3)
or
=SUMPRODUCT(B2:F2,B3:F3)-(E2*E3)


Bottom line is, given the sample numbers you posted, what end result do you want from a formula?
These 3 formulas all give the same result of 39000

=(B2*B3)+(C2*C3)+(D2*D3)+(F2*F3)
=SUMPRODUCT(B2:F2,B3:F3)-SUMPRODUCT(E2,E3)
=SUMPRODUCT(B2:F2,B3:F3)-(E2*E3)
 
Last edited:

kala

Board Regular
Joined
Nov 6, 2002
Messages
65
JonMo, thanks for the breakdown. I get it now (I think I had something wrong with one of the formulas). THANK YOU SO MUCH!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,994
Members
414,418
Latest member
mightyMagnus

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top