Summing row ranges

P

Peter

I have a spreadsheet of many rows. In column A of each row there is a 4 digit
reference number ranging from 0001 to 9999. One number may occure many times
at random. I'd like to sum the values in the rows depending on the a range of
reference numbers eg: sum all rows commencing 1499 to 2499 etc. Can I do this
without macros? Preferably by formula.
Many thanks
Peter
 
J

Jason Morin

Try:

=SUM(OFFSET(A:A,MATCH(num1,A:A,0)-1,1,MATCH
(num2,A:A,0),255))

Array-entered, meaning press ctrl/shift/enter. num1 is
the reference to your first number and num2 is the ref.
the 2nd one.

HTH
Jason
Atlanta, GA
 
R

RagDyer

Try this if you have a single column ( B ) to sum:

=SUMPRODUCT((A1:A100>=1499)*(A1:A100<=2499)*B1:B100)

Can also be used to sum *multiple* columns:

=SUMPRODUCT((A1:A100>=1499)*(A1:A100<=2499)*B1:Z100)

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have a spreadsheet of many rows. In column A of each row there is a 4
digit
reference number ranging from 0001 to 9999. One number may occure many times
at random. I'd like to sum the values in the rows depending on the a range
of
reference numbers eg: sum all rows commencing 1499 to 2499 etc. Can I do
this
without macros? Preferably by formula.
Many thanks
Peter
 
Top