searching for a function

N

Noha

hello everybody, I am using Excel 2003 and have a database of participants
(country, meeting, year, male or female), please help me in finding a
function that sums the number of times that a word (ex. France) appears in a
column "Country" during (ex. 2004) in column "year".

Thank you
 
M

Mike H

Hi,

This assumes your dates are in Column A and France is in column B

=SUMPRODUCT((B1:B20="France")*(YEAR(A1:A20)=2004))

Mike
 
P

Pete_UK

Try something like this:

=SUMPRODUCT((ISNUMBER(SEARCH("France",C1:C100)))*(D1:D100=2004))

I've assumed column C contains countries and column D the year -
adjust to suit.

Hope this helps.

Pete
 
N

Noha

Many thanks Pete, it works. but would you please explain this function: What
is "ISNUMBER" for
 
P

Pete_UK

Glad to hear it worked for you.

I could have suggested (C1:C100="France") as one of the terms, but
this would be looking for exact matches with "France", and if you had
"Northern France" or "South of France" in any of those cells then this
would not have counted them.

By using ISNUMBER/SEARCH it effectively allows you to include a
"contains" clause, or search for "*France*", where * is the anything
wildcard, so it is a bit more flexible.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top