Combining sumIF statements with other functions?
 

I am wondering can a 'sumif statement and vlookup formula' be combined to
provide a total of a range of numbers or maybe even combined with using
'greater than a number but less than another number' functions. For example,
I have a range of 5 digit numbers in column 'A', and have dollar amounts that
correspond to those 5 digit numbers in column 'B', in a seperate tab I need a
formula that would look in coulmn 'A' for a range of numbers (for ex..
greater than 10000 and less than 19999) then give me the total of the
corresponding dollar amounts in column 'B'. Is this possible or am I SOL????

Combining sumIF statements with other functions?
 

Yes, you can do that, and you don't need vlookup. You want something
like this:

=SUMIF(A:A,"<=19999",B:B) - SUMIF(A:A,"<10000",B:B)

The first term adds all the dollar amounts where A is less than or
equal to 19999, and the second term takes away from that the dollar
amounts for when A is less than 10000, effectively giving you the
amounts for when A is in that range.

Hope this helps.

Pete

On Aug 27, 9:51*pm, Hopeless in Cincy <Hopeless in
wrote:
I am wondering can a 'sumif statement and vlookup formula' be combined to
provide a total of a range of numbers or maybe even combined with using
'greater than a number but less than another number' functions. *For example,
I have a range of 5 digit numbers in column 'A', and have dollar amounts that
correspond to those 5 digit numbers in column 'B', in a seperate tab I need a
formula that would look in coulmn 'A' for a range of numbers (for ex..
greater than 10000 and less than 19999) then give me the total of the
corresponding dollar amounts in column 'B'. *Is this possible or am I SOL????

Combining sumIF statements with other functions?
 

greater than 10000 and less than 19999

Taking that literally...

Use cells to hold your boundaries:

D1 = 10000
E1 = 19999

=SUMIF(A1:A10,""&D1,B1:B10)-SUMIF(A1:A10,"="&E1,B1:B10)

Another way...

=SUMPRODUCT(--(A1:A10D1),--(A1:A10<E1),B1:B10)

If you're using Excel 2007:

=SUMIFS(B1:B10,A1:A10,""&D1,A1:A10,"<"&E1)

--
Biff
Microsoft Excel MVP


"Hopeless in Cincy" <Hopeless in wrote in
message ...
I am wondering can a 'sumif statement and vlookup formula' be combined to
provide a total of a range of numbers or maybe even combined with using
'greater than a number but less than another number' functions. For
example,
I have a range of 5 digit numbers in column 'A', and have dollar amounts
that
correspond to those 5 digit numbers in column 'B', in a seperate tab I
need a
formula that would look in coulmn 'A' for a range of numbers (for ex..
greater than 10000 and less than 19999) then give me the total of the
corresponding dollar amounts in column 'B'. Is this possible or am I
SOL????