Forum Discussion
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.
It sounds like the cells with the amounts are text. I recommend arranging the data like this to simplify things:
7 Replies
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")) )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.
It sounds like the cells with the amounts are text. I recommend arranging the data like this to simplify things:
- Thank you! I separated the ranges and was able to figure the rest out. I appreciate your response.
- 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) )- Thank you but that may have just confused me even more...
