Excel Lookup help

[B_vegas]

I am trying to figure out how to do the following. I am trying to get a sum
of numbers in a column, but the numbers need to fit certain criteria. For
example;

I have 500 numbers in a column ranging from 1 to 130
I need the sum of only the numbers between 40 and 60.

I figure that this involves the sum function and some type of "if" and "and"
statement, but i'm not sure how to word this. Could someone Please help!

 

[Ragdyer]

Couple of ways:

=Sumif(A1:A500,">=40")-Sumif(A1:A500,">60")

OR

=Sumproduct((A1:A500>=40)*(A1:A500<=60)*A1:A500)

 

[B_vegas]

Thank you very much!

Couple of ways:

=Sumif(A1:A500,">=40")-Sumif(A1:A500,">60")

OR

=Sumproduct((A1:A500>=40)*(A1:A500<=60)*A1:A500)

 

[joromajr]

-- What if the scenario were the Criteria was in column 1 let us say account
name then the amount was in column 5 and you needed to add all for a
particular account name?

joromajr

 

[Ragdyer]

You could choose from either of the same 2 functions:

=Sumif(A1:A500,"Acctname",E1:E500)

Or

=Sumproduct((A1:A500="Acctname")*E1:E500)

 

[Ragdyer]

You're welcome, and appreciate the feed-back.