Totaling pre-defined groups?

[nummel]

I am not an excel wiz and I have a problem that is hard for me to solve. I
would like to total the # of units for a defined height range. For example:
how many units are within the height range of 38-44? or 46-51? I would like
to have a simple macro that references cell addresses for the min and max
height ranges I wish to total. Below is data examples. The auto filter
works ok, but I want have something that is not so labor intensive to re-use
over and over. I have lots of data and want to try many different
combinations. Thanks in advance...

height # units
38 193
39 318
40 716
41 961
42 997
43 1086
44 2169
45 1867
46 1413
47 1158
48 1017
49 876
50 733
51 401

 

[Biff]

Try this:

Height in column A, A2:A15
Units in column B, B2:B15

Enter your range variables in a couple of cells, say, D1 and E1:

D1 = 38
E1 = 44

=IF(D1>E1,"",SUMIF(A2:A15,">="&D1,B2:B15)-SUMIF(A2:A15,">"&E1,B2:B15))

Biff

 

[Dave Peterson]

I would use another column that groups my Heights.

I'm not quite sure how to determine the categories, though.

If you had said 38-44, 45-51, 52-58, ... (each group spanning 7 whole numbers),
you could use:

=INT((A1-3)/7)*7+3

Then 38-44 would be classified as 38.
45-51 would be 45
52-58 would be 52
.....

Then you could filter on that column.

=======
Another option would be to apply Data|pivottable.

You can group that data by a standard interval and see things like:

Sum of qty
Number Total
31-37 15
38-44 12
45-51 18
52-58 23
59-65 13
66-72 18
73-79 17
80-86 17
87-93 17
94-100 20
101-107 17

Add headers to row 1 (if you don't have them)
select the range A1:Bxxx
Data|pivottable
Follow the wizard until you get to a dialog with a Layout button on it.
Click that layout button
drag the header for the height to the Row field
drag the header for the units to the data field

And finish up the wizard.

Then rightclick on the Height button and choose Group and Show Detail, then
Group.
Then starting at 24 (say)
ending at 197 (or whatever you want)
by: 7

and watch the results.

(You could also do the categorization yourself and then use that in your
pivottable.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[nummel via OfficeKB.com]

Thanks Biff, this will work great for my application, I appreciate the help!

Try this:

Height in column A, A2:A15
Units in column B, B2:B15

Enter your range variables in a couple of cells, say, D1 and E1:

D1 = 38
E1 = 44

=IF(D1>E1,"",SUMIF(A2:A15,">="&D1,B2:B15)-SUMIF(A2:A15,">"&E1,B2:B15))

Biff

I am not an excel wiz and I have a problem that is hard for me to solve. I
would like to total the # of units for a defined height range. For

[quoted text clipped - 23 lines]

50 733
51 401

 

[nummel via OfficeKB.com]

Thanks for the information. I had tried the grouping with the pivot table,
which worked fine as long as your grouping was uniform. The links were full
of good information also. thanks again for your help...

I would use another column that groups my Heights.

I'm not quite sure how to determine the categories, though.

If you had said 38-44, 45-51, 52-58, ... (each group spanning 7 whole numbers),
you could use:

=INT((A1-3)/7)*7+3

Then 38-44 would be classified as 38.
45-51 would be 45
52-58 would be 52
....

Then you could filter on that column.

=======
Another option would be to apply Data|pivottable.

You can group that data by a standard interval and see things like:

Sum of qty
Number Total
31-37 15
38-44 12
45-51 18
52-58 23
59-65 13
66-72 18
73-79 17
80-86 17
87-93 17
94-100 20
101-107 17

Add headers to row 1 (if you don't have them)
select the range A1:Bxxx
Data|pivottable
Follow the wizard until you get to a dialog with a Layout button on it.
Click that layout button
drag the header for the height to the Row field
drag the header for the units to the data field

And finish up the wizard.

Then rightclick on the Height button and choose Group and Show Detail, then
Group.
Then starting at 24 (say)
ending at 197 (or whatever you want)
by: 7

and watch the results.

(You could also do the categorization yourself and then use that in your
pivottable.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

I am not an excel wiz and I have a problem that is hard for me to solve. I
would like to total the # of units for a defined height range. For example:

[quoted text clipped - 20 lines]

50 733
51 401

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thanks Biff, this will work great for my application, I appreciate the
help!

Try this:

Height in column A, A2:A15
Units in column B, B2:B15

Enter your range variables in a couple of cells, say, D1 and E1:

D1 = 38
E1 = 44

=IF(D1>E1,"",SUMIF(A2:A15,">="&D1,B2:B15)-SUMIF(A2:A15,">"&E1,B2:B15))

Biff

I am not an excel wiz and I have a problem that is hard for me to solve.
I
would like to total the # of units for a defined height range. For

[quoted text clipped - 23 lines]

50 733
51 401