SUMPRODUCT criteria

[kala]

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!

 

[Jonmo1]

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]

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]

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)

 

[kala]

JonMo, thanks for the breakdown. I get it now (I think I had something wrong with one of the formulas). THANK YOU SO MUCH!