Grouping and Nesting?

[mcsweeney]

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!!!!

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Field1	Field2	Field3	Field4		SumofField3
2	1234xyz	1990	3	1234		Field2	Field4	Field1	Total
3	1234abc	1997	1	1234		1990-1995	1234	1234xyz	3
4	1246abc	1992	5	1246			1234Total		3
5	1234nmo	1998	6	1234			1246	1246abc	5
6							1246Total		5
7						1996-2001	1234	1234abc	1
8								1234nmo	6
9							1234Total		7
10						GrandTotal			15
11
Sheet1

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

 

[Aladin Akyurek]

Also:

aaCondCounting mcsweeney.xls
	A	B	C	D	E	F	G
1						1990	1996
2	1234xyz	1990	3			1995	2000
3	1234abc	1997	1		1234	3	7
4	1246abc	1992	5		1246	5	0
5	1234nmo	1998	6
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))