Challenge - Countif values in two sheets are different

[romek]

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.
Could you please help? It is quite urgent task. Your support will be greately appreciated.

 

[Special-K99]

Try

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

 

[romek]

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)

 

[XOR LX]

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]

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]

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]

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]

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]

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]

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