# Challenge - Countif values in two sheets are different

#### romek

##### New Member
Hello, I have something like this:

Sheet1
ID | Date
aaa | 5/5/2015
bbb | 3/4/2015

Sheet2
ID | Date
aaa | 8/5/2015
bbb | 3/4/2015

What I need to do is to make a formula that counts number of all occurrences where Date assigned to ID in Sheet1 is different than Date assigned to the same ID in another sheet.
I spent some hours on this and it seems to be a really big challenge. I assume the formula should contain some VLOOKUP, MATCH, INDEX, OFFSET elements, but nothing works for me.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Special-K99

##### Well-known Member
Try

=SUMPRODUCT((A2:A3=Sheet2!A2:A3)*(B2:B3<>Sheet2!B2:B3))

#### romek

##### New Member
Wow! It seems to work great! Thank you very much.
Just one question, do you have any the same formula for conditional formatting that will highlight dates, that are different?
Thanks a lot!

P.S. One more question - the formula works great only if data in both sheets are in the same order. How to enhance it to do the same but even if records are in different order (I prefer to do it directly in formula, without manual sorting first)

Last edited:

#### XOR LX

##### Well-known Member
Hi.

For the case where records may be in a different order in each sheet:

=SUMPRODUCT(N(VLOOKUP(T(IF({1},A2:A3)),Sheet2!A2:B3,2,0)<>B2:B3))

Note that this solution assumes that your IDs are text. If they are in fact numeric, change T in the above to N.

Regards

#### romek

##### New Member
Thanks a lot, this also works great, hoever there is another challenge: I cannot use references like A2:A3, because the number of IDs in column changes so I would like to choose all the column A:A. Unfortunately the formula =SUMPRODUCT(N(VLOOKUP(T(IF({1},A:A)),Sheet1!A:B,2,0)<>B:B)) not works (N/A error) as there are blank cells in the columns. How can I fix it?

#### XOR LX

##### Well-known Member
If you use entire column references in that construction then your system will come close to crashing.

Why can't you choose a suitably large upper range reference, e.g. 100?

Regards

#### romek

##### New Member
Thanks, it is better solution, unfortunately when I extend the numer of rows it again gives N/A error. Maybe because IDs are sometimes numbers and sometimes text values?

#### XOR LX

##### Well-known Member
Yes, most likely. As I said:

Note that this solution assumes that your IDs are text. If they are in fact numeric, change T in the above to N.

Are you able to format the data so that all entries are consistently text, not number?

Regards

#### romek

##### New Member
It does not help due to blank cells, when the last row is for example A199 if works perfect, but when I extend the area to A200, so A200 is blank - there is N/A error.

#### XOR LX

##### Well-known Member
So you need to dynamically define your ranges so that they comprise a range from your first row up to the last row containing data. Do you need help doing this?

Regards

Replies
22
Views
681
Replies
0
Views
641
Replies
7
Views
2K
Replies
1
Views
388
Replies
6
Views
2K

1,196,021
Messages
6,012,904
Members
441,740
Latest member
Latrs

### 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