Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching for a function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching for a function
Hi,
This assumes your dates are in Column A and France is in column B =SUMPRODUCT((B1:B20="France")*(YEAR(A1:A20)=2004)) Mike "Noha" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching for a function
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 On Jan 8, 11:10*am, Noha wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching for a function
Thank you Mike, it does not work. I am displaying this results on another
sheet. "Mike H" wrote: Hi, This assumes your dates are in Column A and France is in column B =SUMPRODUCT((B1:B20="France")*(YEAR(A1:A20)=2004)) Mike "Noha" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching for a function
Many thanks Pete, it works. but would you please explain this function: What
is "ISNUMBER" for "Pete_UK" wrote: 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 On Jan 8, 11:10 am, Noha wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching for a function
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 On Jan 8, 1:39*pm, Noha wrote: Many thanks Pete, it works. but would you please explain this function: What is "ISNUMBER" for "Pete_UK" wrote: 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 On Jan 8, 11:10 am, Noha wrote: 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- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching for a function
again thank you Pete, this is exactly what I needed.
"Pete_UK" wrote: 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 On Jan 8, 1:39 pm, Noha wrote: Many thanks Pete, it works. but would you please explain this function: What is "ISNUMBER" for "Pete_UK" wrote: 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 On Jan 8, 11:10 am, Noha wrote: 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- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching for a function
Glad to help, Noha - thanks for feeding back.
Pete On Jan 8, 2:14*pm, Noha wrote: again thank you Pete, this is exactly what I needed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching specific record using VLOOKUP function. | Excel Worksheet Functions | |||
searching for function | Excel Discussion (Misc queries) | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
changing the default type of searching in the find function | Excel Discussion (Misc queries) | |||
searching | Excel Worksheet Functions |