CountIFS Duplicate finding not Working

[AndyHayes]

I am in the early stages of counting whether a customer has visited us between certain time spans. Is it a unique vist or have we seen the customer before (or are we seeing the same customers)?

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.

 

[steve the fish]

So what is column B meant to show? What do you mean find out if the customer number in A2 appears on both the 2012 & 2014 sheets? That is just a yes/no isn't it? Or if not is it meant to be a count of number of occasions in both years?

Suspecting the latter try this:

=SUM(COUNTIF('2012'!C:C,A2),COUNTIF('2014'!C:C,A2))

 

[AndyHayes]

Hi Mr Fish

Column B is just meant to show whether the customer is on both lists, so I just need a Yes/No answer.

Thanks

Andy

 

[Peter_SSs]

A few further comments.

1. Best not to use whole column references if possible. No use wasting resources checking over 1,000,000 rows if you are only using a few hundred.

2. To test if a customer number appears in both sheets: =AND(COUNTIF('2012'!C$2:C$1000,A2)>0,COUNTIF('2014'!C$2:C$1000,A2)>0)

3. To get the total count on both sheets: As Steve suggested or =COUNTIF('2012'!C$2:C$1000,A2)+COUNTIF('2014'!C$2:C$1000,A2)

4. The reason your formula =COUNTIFS('2012'!C:C,A2,'2014'!C:C,A2) returns less than you expected is that COUNTIFS only counts items in the same relative position in the two ranges. In this case that means the customer code would need to occur on the same rows in '2012' and '2014' to be counted.

 

[steve the fish]

=IF(AND(COUNTIF('2012'!C:C,A2)>0,COUNTIF('2014'!C:C,A2)>0),"Yes","No")

 

[AndyHayes]

A few further comments.

1. Best not to use whole column references if possible. No use wasting resources checking over 1,000,000 rows if you are only using a few hundred..

Yes, I realised it was bad practice, but I couldn't see a way around it!

2. To test if a customer number appears in both sheets: =AND(COUNTIF('2012'!C$2:C$1000,A2)>0,COUNTIF('2014'!C$2:C$1000,A2)>0) ..

You don't need the >0 as this produces the same figure as without the >0

3. To get the total count on both sheets: As Steve suggested or =COUNTIF('2012'!C$2:C$1000,A2)+COUNTIF('2014'!C$2:C$1000,A2) ..

4. The reason your formula =COUNTIFS('2012'!C:C,A2,'2014'!C:C,A2) returns less than you expected is that COUNTIFS only counts items in the same relative position in the two ranges. In this case that means the customer code would need to occur on the same rows in '2012' and '2014' to be counted.

And that is the gem that I was looking for!! I hadn't considered relative positions, and thought that I was being clever by selecting the whole column. It appears that Excel is cleverer!

Thank you so much Peter

Andy

 

[AndyHayes]

=IF(AND(COUNTIF('2012'!C:C,A2)>0,COUNTIF('2014'!C:C,A2)>0),"Yes","No")

Yes this works too.

Thank you very much Mr Fish

 

[steve the fish]

Countifs does this:

Criteria1: Steve
CriteriaRange1: {Steve, Bob, Sue}
ResultArray1: {1,0,0} (True is 1, False is 0)

Criteria2: Fish
CriteriaRange2: {Fish, Dog, Cat}
ResultArray2: {1,0,0}

Excel then looks and effectively multiplys and sums the arrays.

Final Array: {1,0,0} (1*1,0*0,0*0)

Answer=1 (1+0+0)

 

[AndyHayes]

That now makes perfect sense Mr Fish.

Thanks again

Andy