VBA Alternative for sumproduct function

Question

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"

Answer 1

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.

Answer 2

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

Answer 3

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.