Excel - Sum of range 1

[lambic]

Hi,

Sorry if this has been covered before, but I have a column in my spreadsheet which is a list of values, e.g.

4999
1500
6500
6800
7020
e.t.c

I need to sum the values based on range, e.g.

< 5000
5001 - 6000
6001 - 7000
7001 - 8000
e.t.c

Using the above examples:

< 5000 = 6499
6001 - 7000 = 13300
7001 - 8000 = 7020

Can anyone help?

Cheers!

 

[GlennUK]

Have a look at the SUMIF function, which looks like this:

=SUMIF(A1:A5,"<5000")

and do subtraction to get those within a range, like this:

=SUMIF(A1:A5,"<7000")-SUMIF(A1:A5,"<=6001")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SilentAiche]

lambic

Can you clarify a couple of things when you get a moment?

1) Are your sample data and range examples truly representative?

2) Are all of your range groupings in increments of 1000, or do you really have some like "<5,000"? I suspect you do have such ranges or they would not have been in your example, but I have to ask.

2a) Are any of your range criteria really odd numbers, such as from "3331 - 4879"?

3) If by 1,000's, could the groups be 6000-6999 rather than 6001-7000? (it would make things much easier!)

4) Are all values less than 10,000 (i.e., only 4 digits)?

I have an idea, but it depends on these details.

THanks,
Tim

 

[lambic]

Hi,

I think Glenn's suggetsion will do the trick, but to clarify:

The ranges I have are:

0 - 5000
5001 - 6000
6001 - 7000
7001 - 8000
8001 - 9000
9001 - 10000
10001 - 15000
15001 & above

Cheers!

 

[SilentAiche]

Glenn and I were apparently writing at the same time.

Anyhow, Glenn nailed it (again).

So, screw my idea (it wouldn't have worked based on your answers anyway!)

BTW- thanks for addressing my questions after having a solution in hand. That's class.

Best wishes,
Tim

 

[pbrodsky]

Have you considered simply using a vlookup to create categories (in 2nd column) and then either sumif or a pivot table to get the sums?

 

[lambic]

Thanks to everyone who replied. Have sorted it now.

Cheers!

 

[KenWright]

Appreciate your sorted but just for the record there is a function designed to do this called FREQUENCY()

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[GlennUK]

Errrm, Ken .... surely FREQUENCY() simply counts the occurences within a bucket, and doesn't sum those occurrences, doesn't it?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

Oooooooooooooppppppppssssssssssss :-(

Read that question just a tad wrong didn't I.

Cheers Glenn

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------