Count & Sums
 

In Excel I have a spread sheet that in Excel that is constructed as follows.

Col_a Col-b Col_c Col_d
M/C Number Dress No. Of parts Run Number
1 Not Dressed 50 25
2 Not Dressed 48 141
3 Not Dressed 46 500
4 Not Dressed 47 121
4 Dressed 49 122
4 Not Dressed 46 123


I have in column _b a code for the dressing of a m/c. €œDressed€� or €œNot
Dressed€�, which corresponds to a certain run number. (Run Number that m/c
will be dressed)


When Col_b is entered as €œdressed€� I would like to count up 20 rows and sum
the number of parts, and also count down 20 rows to sum the number of parts
this will be put into a matrix to show the number of parts before dress and
after dress.
Eg:

Col_a Col-b Col_c
M/C No. Of parts Before Dress No. Of parts After Dress
4 47 46

Col_a is sorted by ascending order and also the run number.
Is there anyway of doing this ?

Is there only one Dressed?, if so

=SUM(OFFSET($C$2,MATCH("Dressed",B2:B200,0)-1,,-20,))

(adapt to fit your data)

will find the first Dressed, sum 20 rows in C

If there are multiple then you would need to copy down a formula like

=IF(B2="Dressed",SUM(OFFSET($C$2,ROW()-2,,-20,)),"")

Regards,

Peo Sjoblom


"MijC" wrote:

In Excel I have a spread sheet that in Excel that is constructed as follows.

Col_a Col-b Col_c Col_d
M/C Number Dress No. Of parts Run Number
1 Not Dressed 50 25
2 Not Dressed 48 141
3 Not Dressed 46 500
4 Not Dressed 47 121
4 Dressed 49 122
4 Not Dressed 46 123


I have in column _b a code for the dressing of a m/c. €œDressed€� or €œNot
Dressed€�, which corresponds to a certain run number. (Run Number that m/c
will be dressed)


When Col_b is entered as €œdressed€� I would like to count up 20 rows and sum
the number of parts, and also count down 20 rows to sum the number of parts
this will be put into a matrix to show the number of parts before dress and
after dress.
Eg:

Col_a Col-b Col_c
M/C No. Of parts Before Dress No. Of parts After Dress
4 47 46

Col_a is sorted by ascending order and also the run number.
Is there anyway of doing this ?