My Excel 2007 workbook has 3 sheets: -

2012 - Column C has the customer number in it if they have visited (there were 233 visits)

2014 - Column C has the customer number in it if they have visited (there were 321 visits)

Summary - Column A has all the customer number from 1 - 25,000. The data starts at A2

On the Summary sheet I have used a couple more columns: -

Column B - a formula to find out if the customer number in A2 appears on both the 2012 & 2014 sheet in Column C, then copied down 25,000 rows - =COUNTIFS('2012'!C:C,A2,'2014'!C:C,A2)

For some reason this gave me less results than I expected so I added another column: -

Column C - a different formula to find out if the customer number in A2 appears on both the 2012 & 2014 sheet in Column C as a double check of the above,then copied down 25,000 rows - =AND((COUNTIF('2012'!C:C,A2)),(COUNTIF('2014'!C:C,A2)))

I have used C:C as I understand that for CountIFS to work the compared datasets have to be the same size.

The end result for the CountIFS formula is 5

The end result for the AND CountIF is 57 (number of True(s))

Both formulae are definitely copied down the column to cover all 25,000 customers, and scratching my head, reading past posts or asking my colleagues is not helping.

I am missing something obvious here, but for the life of me can't see it.

Thanks in advance for any response. The help I have had from reading this forum and Bill's books, amongst others, has hugely helped me along the way to understanding Excel.