Issue with Duplicate Values when counting data

[sonypsunny]

Given below is a sample of data I work on :


Cov_Ids Name Team


13554 Ajay AUS

13554 Ajay AUS

13554 Ajay AUS

13555 Ajay AUS

13555 Ajay AUS

13555 Ajay AUS

13555 Ajay AUS

13555 Ajay AUS

13556 Allen Europe

13556 Allen Europe

13557 Steve USA

13557 Steve USA

13557 Steve USA

13557 Steve USA

13557 Steve USA

13557 Steve USA

13557 Steve USA

13557 Steve USA

13557 Steve USA

13557 Steve USA

13558 Steve IND


Is it possible to get the count of Ids Steve has done for Team USA ?

(duplicate Id's should not be taken meaning, Result/Output = 1)

 

[oldchippy]

Hi sonypsunny,


Welcome to the forum, I see you have two ID for Steve, so does this work for you


=COUNTIFS(A3:A23,13557,B3:B23,"Steve",C3:C23,"USA")


or this


=SUMPRODUCT(--(A3:A23=13557),--(B3:B23="Steve"),--(C3:C23="USA"))

 

[sonypsunny]

Thanks for the Response


But my problem is I don't want Id's to figure in my Calculations.


It should be with respect to the Name and the Team.


Since the data volume I have is quite huge, using Id's is not practical.


My Summary sheet looks something similar to this:


Steve Allen

USA 1 0

Europe 0 1


Is there any way I can do it?

 

[Faseeh]

Hi Sonypsuny,


Kindly Check out this file, I think it is appropriate.

http://dl.dropbox.com/u/60644346/Counting%20Problem.xlsx


Whenever you enter data kindly update Name/ID/Country Range from Names Manager Menu...


Regards,

Faseeh

 

[sonypsunny]

Thanks a lot!!!!!!!!!!!!


It works wonderful......


Cheers

 

[Faseeh]

Hi sonypsunny,


Glad to see it works!! You are always welcome.


Faseeh