Stuck! How do group these ranges with totals?

mrdeflok · #1 06-13-2017, 06:14 PM

Hi guys,
I have a column of about 17,000 numbers which I need to sort into ranged groups.

I can use sort to get the data manually but it is very time consuming.

Essentially, I want to group the data into ranges as below and get the sum total of each range along with the number of entries which comprise that range.

Ranges......................... Total $......................... # of Accounts
> $50

$25 - $50
$10 - $25
$5 - $10
$1 - $5
$.5 - $1
$.25 - $.5
$.1 - $.25
$.0 - $.1

I have been trying to use something like =SUMIF(A4:A17000,">50") but I am unsure how to get that to work for the next range where it needs to be >10 but <25.

Any help would be hugely appreciated!

Logit · #2 06-13-2017, 07:52 PM

Code:

Col D Formula:			
=SUMIFS(A2:A13,A2:A13,">50",A2:A13,"<500")			
			
Col E Formula :			
=SUMPRODUCT(($A$2:$A$13>50)*($A$2:$A$13<500))			
			
H2 Formula:			
=SUM(A2:A13)			
			
I2 Formula			
=SUM(D2:D10)

The formulas change slightly for each denomination range and # of Accts.

You will need to change the upper range of the formulas to match how many
rows you are using in your workbook. ex: instead of A13 perhaps A500.
.
.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Take String of numbers, expand ranges, sort, then compress back into ranges	AustinBrister	Word VBA	19	08-22-2016 05:18 PM
Group records - multiple IF stmts - Text After Group	baum12	Mail Merge	1	05-25-2015 12:57 AM
Group Table Rows/Column (like Excel Group)	eoinymc	Word	1	03-11-2014 04:51 AM
Range Lookup for Totals? Confused	lawnG	Excel	1	02-01-2011 10:42 AM
Please help with Sum formula to add totals by State! asap	dutch4fire23	Excel	0	07-28-2006 12:41 PM