Count & Sums

[MijC]

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 ?

 

[Peo Sjoblom]

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