SUM array formula question

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

How can I rewrite the following (this does currently work):

=42-(1*SUM((Servers!B2:B2000="DL360")*(Servers!C2:C2000="LCCCR30")))

so that the value DL360 will also be representative of the fact that
Servers!B2:B2000 also contains similar values DL360G1, DL360G2, and DL360G3,
without creating a monstrous formula that would end up looking like this:

=42-SUM(1*SUM((Servers!B2:B2000="DL360")*(Servers!C2:C2000="LCCCR30")),2*SUM
((Servers!B2:B2000="DL380")*(Servers!C2:C2000="LCCCR30")),4*SUM((Servers!B2:
B2000="DL580")*(Servers!C2:C2000="LCCCR30")),2*SUM((Servers!B2:B2000="ML370"
)*(Servers!C2:C2000="LCCCR30")))

This would not be so bad, except for the fact that I also have several other
models that are in the Servers!B2:B2000 column, and therefore this would get
so big that it would be too big for Excel to execute.

Please help, thanks!!
 
=42-SUMPRODUCT((LEFT(Servers!B2:B2000,5)="DL360")+2*(Servers!B2:B2000="DL380")+4*(Servers!B2:B2000="DL580")+2*(Servers!B2:B2000="ML370"),--(Servers!C2:C2000="LCCCR30"))
 
Thanks for your help. This is showing me some great information. However,
the solution you provided did not quite produce an accurate result. Your
solution seemed to resolve for the DL360 models, using the LEFT and 5
arguments, but not for the other models. I do see,though, that I copied and
pasted the wrong formula information in my original posting, so you may have
slightly misinterpreted my goal. Nevertheless, using the information you
sent, I was able to produce 4 separate formulas that I have confirmed work.
They are as follows:

=42-SUMPRODUCT(1*(LEFT(Servers!B2:B2000,5)="DL360"),--(Servers!C2:C2000="LCC
CR30"))
=42-SUMPRODUCT(2*(LEFT(Servers!B2:B2000,5)="DL380"),--(Servers!C2:C2000="LCC
CR30"))
=42-SUMPRODUCT(4*(LEFT(Servers!B2:B2000,5)="DL580"),--(Servers!C2:C2000="LCC
CR30"))
=42-SUMPRODUCT(2*(LEFT(Servers!B2:B2000,5)="ML370"),--(Servers!C2:C2000="LCC
CR30"))

Now, how would this be structured as one concise formula? I think I realize
that in the first item above, the (1* piece does not need to be there for
functionality, it is just there for clarity sake so I don't confuse myself
when looking at this and trying to understand it.

Thanks again!
 
Hi,
Now, how would this be structured as one concise formula? I think I realize
Click to expand...

=42-SUMPRODUCT({1,2,4,2}*(LEFT(Servers!B2:B2000,5)={"DL360","DL380","DL580","ML3
70"})
*(Servers!C2:C2000="LCCCR30"))

Regards,
 
Dan said:
... However,
the solution you provided did not quite produce an accurate result.
Your
solution seemed to resolve for the DL360 models, using the LEFT and 5
arguments, but not for the other models. ...
Click to expand...

Really? The only thing you did different is applying LEFT() to al
models and taking out plusses.
Now, how would this be structured as one concise formula?
Click to expand...

Just return to what I suggested with LEFT() added everywhere or take
look at the suggestion Daniel M. makes
 
Thanks! That helps me out tremendously.

Daniel.M said:
Hi,
realize
=42-SUMPRODUCT({1,2,4,2}*(LEFT(Servers!B2:B2000,5)={"DL360","DL380","DL580",
"ML3
70"})
*(Servers!C2:C2000="LCCCR30"))

Regards,
Click to expand...
 
Thanks again! This helps me out quite a bit.

Aladin Akyurek said:
Really? The only thing you did different is applying LEFT() to all
models and taking out plusses.


Just return to what I suggested with LEFT() added everywhere or take a
look at the suggestion Daniel M. makes.
Click to expand...
 
Back
Top