Help with an array formula

[Harvey Waxman]

I have two columns of numbers in B and C. I need to get the sums for
various value ranges in column B and I use this formula:

{=SUM(IF((B$2:B$1017>$A1027)*(B$2:B$1017<=$A1028),B$2:B$1017))}

where A holds the upper and lower value limits.

I want to sum the values in column C that are in cells adjacent to the
ranges of values in column B.

I can't come up with a formula. Hope this isn't too confusing. Thanks

 

[JE McGimpsey]

I have two columns of numbers in B and C. I need to get the sums for
various value ranges in column B and I use this formula:

{=SUM(IF((B$2:B$1017>$A1027)*(B$2:B$1017<=$A1028),B$2:B$1017))}

where A holds the upper and lower value limits.

I want to sum the values in column C that are in cells adjacent to the
ranges of values in column B.

I can't come up with a formula. Hope this isn't too confusing. Thanks

One way (entered normally):

=SUMPRODUCT(--(B$2:B$1017>$A1027), --(B$2:B$1017<=$A1028), C$2:C$1017)

which is roughly equivalent to (but faster than) the array-entered:

=SUM(IF((B$2:B$1017>$A1027) * (B$2:B$1017<=$A1028), C$2:C$1017))

or, more simply (also array-entered):

=SUM((B$2:B$1017>$A1027) * (B$2:B$1017<=$A1028) * C$2:C$1017)


And, if using XL08, you can use this which is faster than SUMPRODUCT:

=SUMIFS(C$2:C$1017, B$2:B$1017, ">"&A1027, B$2:B$1017, "<="&A1028)

 

[Harvey Waxman]

One way (entered normally):

=SUMPRODUCT(--(B$2:B$1017>$A1027), --(B$2:B$1017<=$A1028), C$2:C$1017)

which is roughly equivalent to (but faster than) the array-entered:

=SUM(IF((B$2:B$1017>$A1027) * (B$2:B$1017<=$A1028), C$2:C$1017))

or, more simply (also array-entered):

=SUM((B$2:B$1017>$A1027) * (B$2:B$1017<=$A1028) * C$2:C$1017)


And, if using XL08, you can use this which is faster than SUMPRODUCT:

=SUMIFS(C$2:C$1017, B$2:B$1017, ">"&A1027, B$2:B$1017, "<="&A1028)

Can't wait to try it, thanks. Using Excel X.

 

[Harvey Waxman]

One way (entered normally):

=SUMPRODUCT(--(B$2:B$1017>$A1027), --(B$2:B$1017<=$A1028), C$2:C$1017)

which is roughly equivalent to (but faster than) the array-entered:

=SUM(IF((B$2:B$1017>$A1027) * (B$2:B$1017<=$A1028), C$2:C$1017))

or, more simply (also array-entered):

=SUM((B$2:B$1017>$A1027) * (B$2:B$1017<=$A1028) * C$2:C$1017)


And, if using XL08, you can use this which is faster than SUMPRODUCT:

=SUMIFS(C$2:C$1017, B$2:B$1017, ">"&A1027, B$2:B$1017, "<="&A1028)

Big help. You're very generous with your time and knowledge. Thanks a
lot.

 

[Carl Witthoft]

One way (entered normally):

=SUMPRODUCT(--(B$2:B$1017>$A1027), --(B$2:B$1017<=$A1028), C$2:C$1017)

or, more simply (also array-entered):

=SUM((B$2:B$1017>$A1027) * (B$2:B$1017<=$A1028) * C$2:C$1017)

[snipped]

Wow. Here I am, a pseudo-expert in Excel, and I never considered
combining logicals. Thanks for the suggestions. To add to what
JEMcG provided, here are a few variations. I am using different sets
of columns, but the setup is the same. So, it's easy to see that "AND"
is implemented with the logical "*" , and "OR" imperfectly with the
logical "+" . The third formula reduces the case when both conditions
are TRUE (which yields a value of 2) to returning either zero or one as
the result of the conditionals.


as above: =SUM((E7:E16>$E$5)*(F7:F16>$F$5)*G7:G16)
try an "OR" =SUM(((E7:E16>$E$5)+(F7:F16>$F$5)) *G7:G16)
that second one will fail if both are true -- returns "2" for sum of two
TRUE

"fix" the 'OR'=SUM((((A7:A16>$A$5)+(B7:B16>$B$5))>0)*C7:C16)
which rejects duplicate 'wins'

Now he'll come up with a better way to do this last one :-(

 

[Harvey Waxman]

To add to what
JEMcG provided, here are a few variations. I am using different sets
of columns, but the setup is the same.

My head is spinning - like a pinball

Thanks and Happy New Year

 

[JE McGimpsey]

Wow. Here I am, a pseudo-expert in Excel, and I never considered
combining logicals. Thanks for the suggestions. To add to what
JEMcG provided, here are a few variations. I am using different sets
of columns, but the setup is the same. So, it's easy to see that "AND"
is implemented with the logical "*" , and "OR" imperfectly with the
logical "+" . The third formula reduces the case when both conditions
are TRUE (which yields a value of 2) to returning either zero or one as
the result of the conditionals.


as above: =SUM((E7:E16>$E$5)*(F7:F16>$F$5)*G7:G16)
try an "OR" =SUM(((E7:E16>$E$5)+(F7:F16>$F$5)) *G7:G16)
that second one will fail if both are true -- returns "2" for sum of two
TRUE

"fix" the 'OR'=SUM((((A7:A16>$A$5)+(B7:B16>$B$5))>0)*C7:C16)
which rejects duplicate 'wins'

Again, I recommend using SUMPRODUCT() rather than the mathematical (not
logical) operators, since (1) there's a performance hit when using the
math operators, (2) the formula doesn't require entering with
CTRL-SHIFT-ENTER or CMD-RETURN, and (3) SUMPRODUCT will ignore text in a
range - which is especially useful if you allow blanks, and your users
"clear" cells with the spacebar.

When using OR's, however, you're pretty much stuck with at least ONE
math operator.

Or's come in two flavors:

a) Inclusive (a OR b OR (both a AND b)):

=SUMPRODUCT(--((A7:A16>$A$5)+(B7:B16>$B$5)>0),C7:C16)

b) Exclusive (a OR b, but NOT (both a AND b)):

=SUMPRODUCT(--((A7:A16>$A$5)+(B7:B16>$B$5)=1),C7:C16)