Optimizing my formula

**Lurr** · 06-21-2011, 11:14 AM

Hello,

I've only been using IF formulas in Excel so bare with me as I'm not very efficient yet. I would like some help with optimizing my formula. I've tried using IFs and ORs within this formula to get the desired result with no success. Here is the formula :

=+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,CHI)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,CZE)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,DAN)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,FIN)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,FR)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,GE)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,GK)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,IT)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,JP)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,LAAM)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,NL)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,UK)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,NOR)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,POL)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,POR)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,RUS)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,SP)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,SWE)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,US)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,Autre)+SUMIFS(M$6:M$900,$B$6:$B$900,"1",$F$6:$F$900,"Pinnacle",$I$6:$I$900,TUR)

So basically, it's just a whole bunch of SUMIFS (21) in total added up to take into consideration that a range of data (I6 to I900) needs to be equal to "IT" OR "SP" OR "JP" etc...

Any help on this would be extremely appreciated, it's taking 15 mins for the file to save.

**Whizbang** · 06-21-2011, 11:32 AM

SUMPRODUCT is a likely candidate

=SUMPRODUCT(($I$6:$I$900="IT")+($I$6:$I$900="SP")+($I$6:$I$900="JP")*($M$6:$M$900))

This link will give you a decent explanation:
http://www.exceluser.com/explore/sumproduct_11.htm

I haven't had a chance to fully review your formula, but this should get your started.

Or, you could use DSum

**shg** · 06-21-2011, 11:39 AM

Welcome to the forum.

Maybe, assuming all those items at the end are named values,

Please Login or Register  to view this content.

**martindwilson** · 06-21-2011, 11:45 AM

i was thinking
sumproduct(--($B$6:$B$900="1"),-($F$6:$F$900="Pinnacle"),--($I$6:$I$900="CHI")+($I$6:$I$900="JAP")+($I$6:$I$900="FRA"),M$6:M$900) but as shg has noticed the chi,tur,por bits dont look like text

**Lurr** · 06-21-2011, 01:58 PM

Thank you for the help but I have not had any success with either suggestion. With shg's suggestion, I get an error (on the 'choose' it looks like) and martindwilson's doesn't seem to add up the fields correctly.

To answer the previous question, yes CHI, JP and all the 21 different names are NAMED VALUES and I only did that to save space in the formula since it was so long.

**daddylonglegs** · 06-21-2011, 02:14 PM

shg's suggestion should work for you, I think, but it requires an opening bracket like ( immediately after CHOOSE and the whole thing has to be wrapped in a SUMPRODUCT.

If the named values are text values then this should also work

=SUMPRODUCT(SUMIFS(M$6:M$900, $B$6:$B$900, 1, $F$6:$F$900, "Pinnacle",$I$6:$I$900, T(INDIRECT({"CHI","CZE","DAN", "FIN", "FR", "GE", "GK", "IT", "JP", "LAAM", "NL", "UK", "NOR", "POL", "POR", "RUS", "SP", "SWE", "US", "Autre", "TUR"}))))

or perhaps simple to have a single range containing all the named values, name that VALUES and use just

=SUMPRODUCT(SUMIFS(M$6:M$900, $B$6:$B$900, 1, $F$6:$F$900, "Pinnacle",$I$6:$I$900, VALUES))

**Lurr** · 06-21-2011, 02:35 PM

That did it!!! Thanks so much!!!

Originally Posted by daddylonglegs

shg's suggestion should work for you, i think, but it requires an opening bracket like ( immediately after choose and the whole thing has to be wrapped in a sumproduct.

If the named values are text values then this should also work

=sumproduct(sumifs(m$6:m$900, $b$6:$b$900, 1, $f$6:$f$900, "pinnacle",$i$6:$i$900, t(indirect({"chi","cze","dan", "fin", "fr", "ge", "gk", "it", "jp", "laam", "nl", "uk", "nor", "pol", "por", "rus", "sp", "swe", "us", "autre", "tur"}))))

or perhaps simple to have a single range containing all the named values, name that values and use just

=sumproduct(sumifs(m$6:m$900, $b$6:$b$900, 1, $f$6:$f$900, "pinnacle",$i$6:$i$900, values))

**shg** · 06-21-2011, 03:32 PM

shg's suggestion should work for you, I think, but it requires an opening bracket like (immediately after CHOOSE) and the whole thing has to be wrapped in a SUMPRODUCT.

I'm thinking that was an overly generous assessment ...