Excel Lookup help

  • Thread starter Thread starter B_vegas
  • Start date Start date
B

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!
 
Couple of ways:

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

OR

=Sumproduct((A1:A500>=40)*(A1:A500<=60)*A1:A500)
 
-- 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
 
You could choose from either of the same 2 functions:

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

Or

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