COUNTIF is not giving exact value

%3CLINGO-SUB%20id%3D%22lingo-sub-2330880%22%20slang%3D%22en-US%22%3ECOUNTIF%20is%20not%20giving%20exact%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2330880%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Friends%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20facing%20a%20problem%2C%20I%20have%20written%20a%20formula%20%3DCOUNTIF(homel!%24A%242%3A%24C%2463830%2C%40%24A%242%3A%24A%243820)%20to%20compare%20data%20in%20two%20column%20in%20two%20different%20sheets.%3C%2FP%3E%3CP%3EThis%20formula%20is%20giving%20the%20value%201%20for%20XCYZ%20when%20it%20is%20finding%20XCYZ.ccy.com.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20want%20that%20it%20should%20throw%200%20for%20such%20situation.%20Please%20let%20me%20know%20what%20modification%20should%20I%20do%20with%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2330880%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2330968%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20is%20not%20giving%20exact%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2330968%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20suggestion%20but%20my%20problem%20is%20not%20that.%20It%20is%20counting%20the%20partial%20result%20also%20which%20is%20not%20required.%3CBR%20%2F%3EDo%20you%20have%20solution%20for%20that%3F%3C%2FLINGO-BODY%3E
New Contributor

Hi Friends,

 

I am facing a problem, I have written a formula =COUNTIF(homel!$A$2:$C$63830,@$A$2:$A$3820) to compare data in two column in two different sheets.

This formula is giving the value 1 for XCYZ when it is finding XCYZ.ccy.com.

 

I just want that it should throw 0 for such situation. Please let me know what modification should I do with the formula.

 

Thanks

 

 

3 Replies

@Siddhu300 Are you sure? It should not. But why not use:

 

=COUNTIF(homel!$A$2:$C$63830,A2) 

 

and copy it down.

It makes no sense (to me at least) to use  @$A$2:$A$3820 as you still have to copy it down. If your Excel version can cope with dynamic arrays, you can omit the @-sign and the results will spill down to 3819 cells in one go.

Thanks for the suggestion but my problem is not that. It is counting the partial result also which is not required.
Do you have solution for that?

@Siddhu300 No I don't, but can you upload an example file with only a few rows demonstrating the "error"? No need to include all 68000 rows.