Excel Formulas

[thifu3]

Hi.

I want to know for example Athif visited so many countries and how to check from the list how many times he visited America.


Athif Maldives

Athif America

Chandoo Singapore

Athif America

Athif India

Athif England

Ali England


Athif to America ????

 

[oldchippy]

Hi,


Either of these will do


=COUNTIFS(A1:A7,"Athif",B1:B7,"America")


=SUMPRODUCT(--(A1:A7="Athif"),--(B1:B7="America"))

 

[thifu3]

thanks oldchippy.

could you help me in this?


Athif Maldives

Athif America 2001 3 times

Chandoo Singapore

Athif America 2010 4 times

Athif India

Athif England

Ali England

Athif America 2012 5 times.


how can i calculate the total times Athif went to America?

 

[Luke M]

Assuming the numbers are stand alone values in column D...


=SUMIFS(D1:D7,A1:A7,"Athif",B1:B7,"America")


=SUMPRODUCT(--(A1:A7="Athif"),--(B1:B7="America"),D1:D7)


At this point, you also might want to consider creating a PivotTable to quickly get sums/counts of all the different countries/people visits.