Once cell checking multiple rows

[Legacy 169354]

Hi,

I am trying to compare two dates by Month and Year only, and if they match I would like to count the total and then place this count into a cell.

the two dates are on another sheet, and the cell that I want the count in for the various months of the year on another.


eg:
(Sheet1)
A
1 Date1 Date2
2 Feb 07 1/2/07
3 mar 09 2/3/09
4 mar 09 29/3/09

Answer should be :
(Sheet2)
jan 07: 0
Feb 09:1
Mar 09:2

etc etc.
I am using excel 2003.

Thanks in advance!

 

[Peter_SSs]

Your example is not all that clear to me. In Sheet1 each of your 3 rows of data do match month & year but I ngather that is not always the case. Could you give a slightly larger and varied sample data set (say 10 rows) and the expected results? Make sure your data demonstrates the different possibilities that can happen.

Can you confirm that all entries that look like dates are actual dates and are just formatted to show month & year in some columns?

 

[Legacy 169354]

Hi Peter,

Here is the pasted data for both sheets. The month data in worksheet1 needs to be counted with the criteria of month+year and then placed in worksheet2 under "strikes". The numbers are correct for the answers in strikes.

Worksheet1 month column will be a variable number as it is dictated by events that occured that month so unfortunately will have no fixed number to count as such, therefore will be dictated by the comparison of dates only. You can see their is two different dates in worksheet1 but the format is the same in column A in both sheets.

Hope I explained it correctly.

WorkSheet1
<table border="0" cellpadding="0" cellspacing="0" width="316"><col style="width: 73pt;" width="97"> <col style="width: 95pt;" width="127"> <col style="width: 69pt;" width="92"> <tbody><tr style="height: 61.5pt;" height="82"> <td class="xl25" style="height: 61.5pt; width: 73pt;" height="82" width="97">Month</td> <td class="xl27" style="border-left: medium none; width: 95pt;" width="127">Ref. No.</td> <td class="xl28" style="border-left: medium none; width: 69pt;" width="92">Date</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Jan-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3239</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">11-Jan-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Jan-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3051</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">11-Jan-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Jan-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3058</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">27-Jan-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">ATSB20048</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">12-Feb-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3106</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">14-Feb-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3084</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">19-Feb-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3085</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">21-Feb-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3330</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">23-Feb-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3331</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">25-Feb-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3352</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">28-Feb-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Mar-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3228</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">14-Mar-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3226</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">08-Apr-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">ATSB200702707</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">18-Apr-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3401</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">20-Apr-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3105</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">27-Apr-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">3103</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">27-Apr-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">May-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">ATSB02707</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">01-May-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">May-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">ATSB3292</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">27-May-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">May-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">ATSB203428</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">29-May-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">May-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">ATSB23429</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">29-May-07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">May-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 95pt;" width="127">ATSB203479</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">31-May-07</td> </tr> </tbody></table>

WorkSheet2 (where the answers to the count should go under column Strikes)
<table border="0" cellpadding="0" cellspacing="0" width="186"><col style="width: 91pt;" width="121"> <col style="width: 49pt;" width="65"> <tbody><tr style="height: 51.75pt;" height="69"> <td class="xl26" style="height: 51.75pt; width: 91pt;" height="69" width="121">Month</td> <td class="xl29" style="width: 49pt;" width="65">Strikes</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 91pt;" height="17" width="121">Jan-07</td> <td class="xl30" style="width: 49pt;" width="65">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Feb-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Mar-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65"> 1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Apr-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">May-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65"> 5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Jun-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Jul-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Aug-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Sep-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Oct-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Nov-07</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl28" style="height: 13.5pt; border-top: medium none; width: 91pt;" height="18" width="121">Dec-07</td> <td class="xl33" style="border-top: medium none; width: 49pt;" width="65"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 91pt;" height="17" width="121">Jan-08</td> <td class="xl30" style="width: 49pt;" width="65">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none; width: 91pt;" height="17" width="121">Feb-08</td> <td class="xl31" style="border-top: medium none; width: 49pt;" width="65">1</td> </tr> </tbody></table>

Thanks!!!!

 

[Legacy 169354]

forgot to add that I will have to add an extra column to worksheet1 that needs to be referenced as well.

so, not only will the Date (month & Year) have to be checked but also the "port" has to be checked to be the same, in this example "ABX"

That column is here:

Worksheet1:
<table border="0" cellpadding="0" cellspacing="0" width="459"><col style="width: 73pt;" width="97"> <col style="width: 95pt;" width="127"> <col style="width: 69pt;" width="92"> <col style="width: 54pt;" width="72"> <col style="width: 53pt;" width="71"> <tbody><tr style="height: 61.5pt;" height="82"> <td class="xl49" style="height: 61.5pt; width: 73pt;" height="82" width="97">Month</td> <td class="xl32" style="border-left: medium none; width: 69pt;" width="92">Date</td> <td class="xl42" style="border-left: medium none; width: 53pt;" width="71">Port</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Jan-07</td> <td class="xl28" style="border-left: medium none; width: 69pt;" width="92">11-Jan-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">ABX</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Jan-07</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">11-Jan-07</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">ADL</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl51" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Jan-07</td> <td class="xl30" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">27-Jan-07</td> <td class="xl31" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">ADL</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl40" style="height: 12.75pt; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl28" style="border-left: medium none; width: 69pt;" width="92">12-Feb-07</td> <td class="xl29" style="border-left: medium none; width: 53pt;" width="71">ABX</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">14-Feb-07</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">ABX</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">19-Feb-07</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">LSY</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">21-Feb-07</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">LSY</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">23-Feb-07</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">LSY</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">25-Feb-07</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">ABX</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Feb-07</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">28-Feb-07</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">SYD</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl51" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Mar-07</td> <td class="xl34" style="border-left: medium none; width: 69pt;" width="92">14-Mar-07</td> <td class="xl33" style="border-left: medium none; width: 53pt;" width="71">GFN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl40" style="height: 12.75pt; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl28" style="border-left: medium none; width: 69pt;" width="92">08-Apr-07</td> <td class="xl29" style="border-left: medium none; width: 53pt;" width="71">MEL</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">18-Apr-07</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">ABX</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">20-Apr-07</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">GFN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl50" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">27-Apr-07</td> <td class="xl25" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">ABX</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl51" style="height: 12.75pt; border-top: medium none; width: 73pt;" height="17" width="97">Apr-07</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">27-Apr-07</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">SYD</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl40" style="height: 12.75pt; width: 73pt;" height="17" width="97">May-07</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">01-May-07</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 53pt;" width="71">ABX</td> </tr> </tbody></table>

Worksheet2:

<table border="0" cellpadding="0" cellspacing="0" width="380"><col style="width: 76pt;" width="101"> <col style="width: 95pt;" width="127"> <col style="width: 49pt;" width="65"> <col style="width: 65pt;" width="87"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt; width: 76pt;" height="18" width="101">
</td> <td colspan="1" class="xl36" style="width: 209pt;" width="279">ABX</td> </tr> <tr style="height: 51.75pt;" height="69"> <td class="xl27" style="height: 51.75pt; width: 76pt;" height="69" width="101">Month</td> <td class="xl32" style="border-top: medium none; width: 95pt;" width="127">Strikes</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 76pt;" height="17" width="101">Jan-07</td> <td class="xl39" style="width: 95pt;" width="127">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 76pt;" height="17" width="101">Feb-07</td> <td class="xl29" style="border-top: medium none; width: 95pt;" width="127">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 76pt;" height="17" width="101">Mar-07</td> <td class="xl29" style="border-top: medium none; width: 95pt;" width="127">0
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 76pt;" height="17" width="101">Apr-07</td> <td class="xl29" style="border-top: medium none; width: 95pt;" width="127">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 76pt;" height="17" width="101">May-07</td> <td class="xl29" style="border-top: medium none; width: 95pt;" width="127">2
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none; width: 76pt;" height="17" width="101">Jun-07</td> <td class="xl29" style="border-top: medium none; width: 95pt;" width="127">0
</td> </tr> </tbody></table>

Thanks

 

[Peter_SSs]

With Sheet1 like this ..

Excel Workbook
	A	B	C	D
1	Month	Ref. No.	Date	Port
2	Jan-07		11-Jan-07	ABX
3	Jan-07		11-Jan-07	ADL
4	Jan-07		27-Jan-07	ADL
5	Feb-07		12-Feb-07	ABX
6	Feb-07		14-Feb-07	ABX
7	Feb-07		19-Feb-07	LSY
8	Feb-07		21-Feb-07	LSY
9	Feb-07		23-Feb-07	LSY
10	Feb-07		25-Feb-07	ABX
11	Feb-07		28-Feb-07	SYD
12	Mar-07		14-Mar-07	GFN
13	Apr-07		8-Apr-07	MEL
14	Apr-07		18-Apr-07	ABX
15	Apr-07		20-Apr-07	GFN
16	Apr-07		27-Apr-07	ABX
17	Apr-07		27-Apr-07	SYD
18	May-07		1-May-07	ABX
19
Sheet1

.. try this (copied down) in Sheet2. You may need to adjust the range to ensure it covers your Sheet1 data rows before copying the formula down.

Excel Workbook
	A	B
1		ABX
2
3	Month	Strikes
4	Jan-07	1
5	Feb-07	3
6	Mar-07	0
7	Apr-07	2
8	May-07	1
9	Jun-07	0
10
Sheet2

 

[Peter_SSs]

You could also use a Pivot Table (found in the Data menu) to summarise your data. Here I have done it on the same sheet but you can create a Pivot Table on a separate sheet if you want.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	Ref. No.	Date	Port		Count of Port	Port
2	Jan-07		11-Jan-07	ABX		Date	ABX	ADL	GFN	LSY	MEL	SYD	Grand Total
3	Jan-07		11-Jan-07	ADL		Jan	1	2					3
4	Jan-07		27-Jan-07	ADL		Feb	3			3		1	7
5	Feb-07		12-Feb-07	ABX		Mar			1				1
6	Feb-07		14-Feb-07	ABX		Apr	2		1		1	1	5
7	Feb-07		19-Feb-07	LSY		May	1						1
8	Feb-07		21-Feb-07	LSY		Grand Total	7	2	2	3	1	2	17
9	Feb-07		23-Feb-07	LSY
10	Feb-07		25-Feb-07	ABX
11	Feb-07		28-Feb-07	SYD
12	Mar-07		14-Mar-07	GFN
13	Apr-07		8-Apr-07	MEL
14	Apr-07		18-Apr-07	ABX
15	Apr-07		20-Apr-07	GFN
16	Apr-07		27-Apr-07	ABX
17	Apr-07		27-Apr-07	SYD
18	May-07		1-May-07	ABX
19
Sheet1

 

[Legacy 169354]

Thanks Peter! Its 12 am in Sydney and I was up by sheer luck! I will try this when I get to work in 8 hrs

Thanks Again!

 

[Peter_SSs]

Its 12 am in Sydney

I'm in that time zone too.

 

[Legacy 169354]

Thank you very much Peter! Worked great!

I now have the Excel tables to the web >> Excel Jeanie HTML 4 installed! I was wondering how people did that!

I also never new the text function could do that so thaks for saving my brain fromn exploding!