SOLVED

Sum

Copper Contributor

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
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

@Awright91 

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

Patrick2788_0-1693499150000.png

 

Thank you but that may have just confused me even more...
Thank you! I separated the ranges and was able to figure the rest out. I appreciate your response.

@Patrick2788 

 

can't make SUM work  Returns last number in column

@Awright91 

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.

@Awright91 

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"))
  )

image.png

 

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

@Awright91 

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

Patrick2788_0-1693499150000.png

 

View solution in original post