Formula Query - COUNTIF? Column A contains word and Column B contains word

Any advice would be hugely appreciated:

Column A details year numbers in the format 2013, 2014 etc

Column B details names e.g. John Smith
N.B. sometimes the cell will contain more than one name e.g John Smith, Jane Smith

I want to count how many times John Smith appears in column B if column A shows 2013 (on the same row). This needs to take into account as detailed above that Column B may sometimes contain more than one name.

Please help!

sumproduct(IF(A1:A100="2013",1,0)*IF(NOT(ISERROR(FIND(1,"JOHN SMITH",A1:A100)))))
I think, it's an array formula so confirm with ctrl+shift+enter.

Originally Posted by daffodil11

=COUNTIFS(A1:A5000,2013,B1:B5000,"*John Smith*")

* = Wildcard value. In this expression is means anything+"John Smith"+anything. It would even count John Smith in "djfjohn smith132srs"

Thanks for the response - This worked great. If only the function was recognised in Google Spreadsheets! I'll just have to export the data to Excel. (Company's decision to use all things Google)

Originally Posted by arlu1201

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

Thank you for your comment however the language used is not the most friendly. I've just finished working after 28 hours straight on a time critical project. I have not had the opportunity to catch up with my admins.

That being said I will now be thanking those who helped.