sum or sum if?

[crmuha]

I have a list of $ values, and I need to find the sum of those that are greater than -( negative)$5 and less than $5. PLEASE HELP!

 

[bobhc]

Good day crmuha


Try this


=SUMPRODUCT((A1:A20>-5)*(A1:A20<5))

 

[Debraj]

Hi crmuha,


SUM

=SUM(IF((A1:A20>-5)*(A1:A20<5),A1:A20))[i] Ctrl + Shift + Enter

[b] SUMIFS [/b]

[code]=SUMIFS(A1:A20,A1:A20,">"&-5,A1:A20,"<"&5)

In case of Excel 2007+[/i]

SUMPRODUCT

little modification in bobhc's formula

=SUMPRODUCT((A1:A20>-5)*(A1:A20<5),A1:A20)[/code]


Regards,

Deb

 

[bobhc]

Good day Deb


Thanks for the modification I was just giving the number of instances of >-5 and <5 and not the sum total

 

[Colin Legg]

If you're using (or require compatability with) pre Excel 2007 (so SUMIFS() is unavailable) then you can also do it with SUMIF():

[pre]

=SUMIF(A1:A20,">-5")-SUMIF(A1:A20,">=5")

[/pre]

 

[roygargi]

unable to understand. plz clear it

 

[Debraj]

Hi Gargi..


Its third time.. Only single line.. "UNABLE TO UNDERSTAND"..

but never clear.. what exactly.. you are not able to understand..


Please search in the top right google search bar.. what function you are unable to grasp..

It will gives you some good topic.. for basic things.. if you need some customize function.. we are here for you..


Regards,

Deb

 

[roygargi]

@ Debraj Roy --- jst for ur info- m unable to understand sumif formula. got it

 

[SirJB7]

Hi, roygargi!


I've got a solution for your SUMIF issue to understand it. Try this:

a) open Excel (after turning on the computer, identifying yourself if needed, waiting for startup process to end)

b) open a new workbook if it's not automatically done

c) in any cell type:

=SUMIF(

and press the function symbol "f(x)" at the left of the edit formula text box (aka the place where you enter values into cells and write formulas)

d) click on "Help about this function" at the lower left blue link

e) if any issue in the process go to this link:

http://office.microsoft.com/en-us/excel-help/sumif-function-HP010342932.aspx


Regards!