SUM array formula question
 

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

=42-(1*SUM((Servers!B2:B2000="DL360")*(Servers!C2:C200 0="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:C 2000="LCCCR30")),2*SUM
((Servers!B2:B2000="DL380")*(Servers!C2:C2000="LCC CR30")),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*(S ervers!B2:B2000="DL380")+4*(Servers!B2:B2000="DL58 0")+2*(Servers!B2:B2000="ML370"),--(Servers!C2:C2000="LCCCR30"))

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

=42-(1*SUM((Servers!B2:B2000="DL360")*(Servers!C2:C200 0="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:C 2000="LCCCR30")),2*SUM
((Servers!B2:B2000="DL380")*(Servers!C2:C2000="LCC CR30")),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!!


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275986

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!


"Aladin Akyurek" wrote in
message ...


=42-SUMPRODUCT((LEFT(Servers!B2:B2000,5)="DL360")+2*(S ervers!B2:B2000="DL380
")+4*(Servers!B2:B2000="DL580")+2*(Servers!B2:B200 0="ML370"),--(Servers!C2:C
2000="LCCCR30"))

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

=42-(1*SUM((Servers!B2:B2000="DL360")*(Servers!C2:C200 0="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:C 2000="LCCCR30")),2*SUM

((Servers!B2:B2000="DL380")*(Servers!C2:C2000="LCC CR30")),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!!


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275986

Hi,

Now, how would this be structured as one concise formula? I think I realize

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

Regards,

Dan Wrote:
... 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. ...

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

Now, how would this be structured as one concise formula?

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


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275986

Thanks! That helps me out tremendously.

"Daniel.M" wrote in message
...
Hi,

Now, how would this be structured as one concise formula? I think I
realize


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

Regards,

Thanks again! This helps me out quite a bit.

"Aladin Akyurek" wrote in
message ...

Dan Wrote:
... 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. ...

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

Now, how would this be structured as one concise formula?

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


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275986