CODEWITHSUNDEEP
vbaexcelexcel-formulasum
L.J.G
L.J.G

Reputation: 79

VBA Alternative for sumproduct function

I tried to use SUMIFS, but it failed since the formula doesn't allow me to use two different criterias from the same criteria range (which makes sense of course), so I looked around the Internet and found '=SUMPRODUCT' instead I used it but it doesn't return any value. Here my formula

=SUMPRODUCT((('BB_Juni 2016_Crew'!E:E="DB")+('BB_Juni 2016_Crew'!E:E="DZ"))*('BB_Juni 2016_Crew'!G:G))

Maybe Looping through the range with an if clause and using the sum function after "then"?

In row number 26 & 27 are two different string values ("DB,DZ") and in the same row is a value in the column (Betrag) which means amount and I need a formula to sum all values that are in the same rows as "DB" and "DZ"

Upvotes: 2

Views: 706

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149287

I agree with Jeeped that you should "Cut your full column references down to the actual extents of your data"

There is a slight typo in your formula. Replace the * with a , and it will work just fine :)

Your original formula should be

=SUMPRODUCT((('BB_Juni 2016_Crew'!E1:E6="DB")+('BB_Juni 2016_Crew'!E1:E6="DZ")), 'BB_Juni 2016_Crew'!G1:G6)

Replace 6 with the relevant row.

enter image description here

Upvotes: 1

user4039065
user4039065

Reputation:

This is the best I can improve your existing formula without more information.

=SUMPRODUCT(('BB_Juni 2016_Crew'!E1:INDEX('BB_Juni 2016_Crew'!E:E, MATCH("zzz", 'BB_Juni 2016_Crew'!E:E))={"DB","DZ"})*('BB_Juni 2016_Crew'!G1:INDEX('BB_Juni 2016_Crew'!G:G, MATCH("zzz", 'BB_Juni 2016_Crew'!E:E))))

It may look more complicated but it actually performs much less work.

Actually, this may be even better.

=SUM(SUMIFS('BB_Juni 2016_Crew'!G:G, 'BB_Juni 2016_Crew'!E:E, {"DB","DZ"}))

Both are standard formulas (no need for CSE).

Upvotes: 1

FDavidov
FDavidov

Reputation: 3675

There is a spreadsheet function AND that accepts several conditions and returns TRUE of all conditions are TRUE, FALSE otherwise.

You may try re-phrasing your formula or make your question clearer so it may be possible to give you an actual working formula.

Upvotes: 0

Related Questions