Wrong result when using <>

[marc01]

Hi all

I have a raw data table and using sum and countifs to calculate answers.

The 2 following formula should in theory return the same answer however the first formula gives the correct answer, but the second formula using not equal to (<>) for 'point won by' is giving the wrong answer (keeps giving me 1 more than it should be)

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=COUNTIFS(Table1[Server],Summary!$C$2,Table1[1st serve],"T",Table1[Serve+1 Hand],"<>",Table1[Serve+1 Situation],"Neutral",Table1[Point won by],F2)

[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=COUNTIFS(Table1[Server],Summary!$C$2,Table1[1st serve],"T",Table1[Serve+1 Hand],"<>",Table1[Serve+1 Situation],"Neutral",Table1[Point won by],"<>C2")[/FONT]

[/FONT]

Even when I change the 'point won by' criteria to "Roger Federer" for example, it works correctly, but not for "<>C2" where C2 may be Rafa Nadal and the only other raw data is Roger Federer. Any ideas on what is going on?

Thanks,

Marc

 

[Toadstool]

Are you sure you want

[FONT=Verdana][FONT=Verdana]"<>C2"[/FONT][/FONT]

and not

[FONT=Verdana][FONT=Verdana]"<>"&C2[/FONT][/FONT]

?

 

[marc01]

Brilliant that works! Many thanks for your help

 

[Toadstool]

You're welcome!