# Grouping and Nesting?

#### mcsweeney

##### New Member
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

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

Replies
2
Views
284
Replies
4
Views
481
Replies
2
Views
465
Replies
7
Views
620
Replies
1
Views
232

1,218,944
Messages
6,145,358
Members
450,611
Latest member
JodiWe

### 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.

### Which adblocker are you using?

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

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