# CountIFS Duplicate finding not Working

#### AndyHayes

##### Board Regular
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.

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
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))

Last edited:
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

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.

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

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

=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

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)

That now makes perfect sense Mr Fish.

Thanks again

Andy

Replies
15
Views
353
Replies
1
Views
314
Replies
10
Views
973
Replies
1
Views
195
Replies
2
Views
212

1,196,206
Messages
6,014,011
Members
441,802
Latest member
Aneurysm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back