SOLVED

# Sum

Copper Contributor

# Sum

Is there a way to get the sum value for number ranges for example calculating income for James \$21,000 - \$26,000, Mary \$15,000 - \$20,000, and Mark \$50,000 - \$55,000. I want to add these income ranges using a formula but am unsure if this is possible, since when I attempt to calculate them by using the formula =SUM(A2:A4) the answer keeps coming back to \$0.

7 Replies

# Re: Sum

If the SUM is coming back 0 that probably means those cells contain TEXT and not VALUES. Make sure those cells have actual values. you can test this using ISNUMBER(). you can try to have excel force a cell formatted as text to be a number by doing math on it or using N( ) or VALUE( ). The simplest might be =SUM(--(A2:A4))
As for the RANGES you can use SUMIF or SUMIFS. I recommend SUMIFS because the format makes more sense to me and allows you to easily expand for more IFs and if you are using a range you will need more than 1 IF condition. =SUMIFS(A2:A4, A2:A4, ">"&21000, A2:A4, "<="&26000) but NOTE SUMIFS must use RANGE and not calculated arrays so if you need to convert A2:A4 into numbers (e.g. --(A2:A4) ) then you will need something else like SUMPRODUCT something like this:
=SUMPRODUCT( (A2:A4)*(--(A2:A4)>21000)*(--(A2:A4)<=26000) )
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Sum

It sounds like the cells with the amounts are text.  I recommend arranging the data like this to simplify things:

# Re: Sum

Thank you but that may have just confused me even more...

# Re: Sum

Thank you! I separated the ranges and was able to figure the rest out. I appreciate your response.

# Re: Sum

can't make SUM work  Returns last number in column

# Re: Sum

This is unlikely to be the best solution for you, but there is a trick that allows number ranges separated with minus (hyphen) to be broken apart.

``````"Lower values"
= IMREAL(range&"i")

"Upper values"
= -IMAGINARY(range&"i")``````

First, however, you would need to strip out the dollar signs since they stop the strings being recognised as numbers.

# Re: Sum

The result is pretty crazy, but it works

``````= LET(
string, SUBSTITUTE(range, "\$", "") & "i",
lower,  IMREAL(string),
upper, -IMAGINARY(string),
CONCATENATE(TEXT(SUM(lower),"\$#,##0"), "-", TEXT(SUM(upper),"\$#,##0"))
)``````

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Sum

It sounds like the cells with the amounts are text.  I recommend arranging the data like this to simplify things: