Creating a summary table using formula

**arbrahul** · 05-29-2012, 09:52 AM

I am very new to excel and don know much about formulas. I am struggling to create a small excel sheet as attached. In the main table there wil be many columns. First column will the the patch number. The rest of the columns are for my testing kits and will contain the date on which the patch has been installed on the kit. Next table has the summary of the patchs installed on all the kits. How could I automatically populate the information in the second table ?? Any help on this would be really apreciated.
sample spready attached.

Thank you

**Fotis1991** · 05-29-2012, 10:42 AM

Hi

I am not sure what you mean by "Last Patch". Is Number 7, corect for KIT A, or is 10 =COUNTA(B4:B13)?

For "No of Patches", use these.

=COUNT(B4:B13)

=COUNT(c4:c13)

=COUNT(d4:d13)

Does this, helps you?

**singerj6** · 05-29-2012, 12:15 PM

I think this is what you want...

Last Patch Kit A, Cell H5

=IF(COUNTIF(B4:B13,MAX(B4:B13))>1,INDEX(A4:D13,MATCH(MAX(B4:B13),B4:B13,),1)+(COUNTIF(B4:B13,MAX(B4:B13))-1),INDEX(A4:D13,MATCH(MAX(B4:B13),B4:B13,),1))

Last Patch Kit B, Cell H6

=IF(COUNTIF(C4:C13,MAX(C4:C13))>1,INDEX(A4:D13,MATCH(MAX(C4:C13),C4:C13,),1)+(COUNTIF(C4:C13,MAX(C4:C13))-1),INDEX(A4:D13,MATCH(MAX(C4:C13),C4:C13,),1))

Last Patch Kit C, Cell H7

=IF(COUNTIF(D4:D13,MAX(D4:D13))>1,INDEX(A4:D13,MATCH(MAX(D4:D13),D4:D13,),1)+(COUNTIF(D4:D13,MAX(D4:D13))-1),INDEX(A4:D13,MATCH(MAX(D4:D13),D4:D13,),1))

If this has helped, please add to my rep by clicking the star in the bottom left.