Grouping and Nesting?

mcsweeney

New Member
Joined
Jul 15, 2002
Messages
3
We have a spreadsheet with 3 columns.
Column1 contains 7 digit seriel numbers
Column2 contains years (1990 - 2000)
Column3 contains units

It would look similar to below:
1234xyz 1990 3
1234abc 1997 1
1246abc 1992 5
1234nmo 1998 6

We need to see how many units we sold for years 1990-1995 and 1996-2000 for seriel numbers where the first 4 digits of the seriel number match. Desired outcome:
1234xyz 1990 3
total units=3
1234abc 1997 1
1234nmo 1998 6
total units=7
1246abc 1992 5
total units=5

We know there is a way to do this - grouping and subtotaling? - but the Help in Excel is baffling us? Could you help?

THANKS!!!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Book1
ABCDEFGHIJKL
1Field1Field2Field3Field4SumofField3
21234xyz199031234Field2Field4Field1Total
31234abc1997112341990-199512341234xyz3
41246abc1992512461234Total3
51234nmo19986123412461246abc5
61246Total5
71996-200112341234abc1
81234nmo6
91234Total7
10GrandTotal15
11
Sheet1

This message was edited by Mark W. on 2002-09-17 12:44
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Also:
aaCondCounting mcsweeney.xls
ABCDEFG
119901996
21234xyz1990319952000
31234abc19971123437
41246abc19925124650
51234nmo19986
6
Sheet1


The formula in F3:

=SUMPRODUCT((LEFT($A$2:$A$5,4)=$E3&"")*($B$2:$B$5>=F$1)*($B$2:$B$5<=F$2)*($C$2:$C$5))
 

Forum statistics

Threads
1,147,498
Messages
5,741,505
Members
423,663
Latest member
kaveh87rsh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top