Forum Discussion
Solved
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...
It sounds like the cells with the amounts are text. I recommend arranging the data like this to simplify things:
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) )
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...
