Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to sum multiple occurrences of a word in a table of texts
Hi,
I'm stumped on this one but I'm sure that it's doable. I have a table of data based on Towns and Dates. Let's say there are 10 Towns from B10 to B19 and Dates are from C9 to I9 The table data is text extracts from diary entries for each Date in each Town. The data may (or may not) include remarks about the weather. (Isn't this interesting!). Basically, I want to be able to count instances of the word "rain" in the data entries and display the sum of occurrences horizontally (by Town) in column A and vertically (by Date) in row 7. Thanks in advance for any advice. Zimina |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to sum multiple occurrences of a word in a table of texts
Hi
Supposing the text you want to look at is all contained in column A and the value being searched "rain" is held in D1 In cell C10 =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND(B$10,$A$1:$A$1000)))* (ISNUMBER(FIND($C10,$A$1:$A$1000)))) -- Regards Roger Govier "Zimina" wrote in message ... Hi, I'm stumped on this one but I'm sure that it's doable. I have a table of data based on Towns and Dates. Let's say there are 10 Towns from B10 to B19 and Dates are from C9 to I9 The table data is text extracts from diary entries for each Date in each Town. The data may (or may not) include remarks about the weather. (Isn't this interesting!). Basically, I want to be able to count instances of the word "rain" in the data entries and display the sum of occurrences horizontally (by Town) in column A and vertically (by Date) in row 7. Thanks in advance for any advice. Zimina |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to sum multiple occurrences of a word in a table of texts
Hi Zimina
I answered the post just before leaving to go on a trip, and as soon as I got in the car I knew I had made an some errors. Please accept my apologies Firstly the comparison should have been against C$9 as that contains the first of the Dates, and all the others are on row 9. The Relativity of row 9 should have been C$9 as the row needs to stay absolute with the column varying as it is copied across For the Towns, starting in B10 it is the B that has to stay Absolute and the row relative as it is copied down, so it should be $B10 Also, unless the dates that you have typed in row 9 are Text representations of the date, then the comparison will fail, as the dates within your "diary text" will be Text. Assuming the dates in your diary are in the form 28 Nov 2006 as a piece of text, then the values in C9 onward would need to be "28 Nov 2006" and not 28/11/2006 as a true Excel date, as that will be stored internally as serial number 39049. If you do use text dates in row 9 or the same format as you have type in the diary, then the following formula entered in C10 does work. =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND($B10,$A$1:$A$1000)))* (ISNUMBER(FIND(C$9,$A$1:$A$1000)))) Copy across through D10:I10, then copy the whole of C10:I10 down through C11:C19 If you have Excel dates in row 9, then you would need to convert this within the formula to the same format as appears in your diary text. I have assumed the UK format of dd mmm yyyy. In which case In C10 use =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND($B10,$A$1:$A$1000)))* (ISNUMBER(FIND(TEXT(C$9,"dd mmm yyyy"),$A$1:$A$1000)))) And why are we multiplying matrices ? That's the way Sumproduct works. Each set of tests will produce True or False Firstly for each cell in the range A1:A1000 can "rain" be found, True or False Next for each of those cells can the Town be found Then can the Date be found. So we would end up with something like T,F,T,F,F,F,F F,F,T,T,F,T,F T,F,T,F,T,F,T When we do the multiplication, the Trues are coerced to 1's and the Falses to 0's so we get 1,0,1,0,0,0,0 0,0,1,1,0,1,0 1,0,1,0,1,0,1 which when multiplied becomes 0,0,1,0,0,0,0 Which Sumproduct would then sum to 1 For more explanation on how Sumproduct works, take a look at Bob Phillips excellent treatise on the subject at http://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Zimina" wrote in message ... Sorry Roger, I don't understand the reply. What's happening in B$10 and C$10 ? And why are we multiplying matrices ? TIA Zimina "Roger Govier" a écrit dans le message de news: ... Hi Supposing the text you want to look at is all contained in column A and the value being searched "rain" is held in D1 In cell C10 =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND(B$10,$A$1:$A$1000)))* (ISNUMBER(FIND($C10,$A$1:$A$1000)))) -- Regards Roger Govier "Zimina" wrote in message ... Hi, I'm stumped on this one but I'm sure that it's doable. I have a table of data based on Towns and Dates. Let's say there are 10 Towns from B10 to B19 and Dates are from C9 to I9 The table data is text extracts from diary entries for each Date in each Town. The data may (or may not) include remarks about the weather. (Isn't this interesting!). Basically, I want to be able to count instances of the word "rain" in the data entries and display the sum of occurrences horizontally (by Town) in column A and vertically (by Date) in row 7. Thanks in advance for any advice. Zimina |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to sum multiple occurrences of a word in a table of texts
Thanks Roger.
Wilco. Over and out. Zimina "Roger Govier" a écrit dans le message de news: ... Hi Zimina I answered the post just before leaving to go on a trip, and as soon as I got in the car I knew I had made an some errors. Please accept my apologies Firstly the comparison should have been against C$9 as that contains the first of the Dates, and all the others are on row 9. The Relativity of row 9 should have been C$9 as the row needs to stay absolute with the column varying as it is copied across For the Towns, starting in B10 it is the B that has to stay Absolute and the row relative as it is copied down, so it should be $B10 Also, unless the dates that you have typed in row 9 are Text representations of the date, then the comparison will fail, as the dates within your "diary text" will be Text. Assuming the dates in your diary are in the form 28 Nov 2006 as a piece of text, then the values in C9 onward would need to be "28 Nov 2006" and not 28/11/2006 as a true Excel date, as that will be stored internally as serial number 39049. If you do use text dates in row 9 or the same format as you have type in the diary, then the following formula entered in C10 does work. =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND($B10,$A$1:$A$1000)))* (ISNUMBER(FIND(C$9,$A$1:$A$1000)))) Copy across through D10:I10, then copy the whole of C10:I10 down through C11:C19 If you have Excel dates in row 9, then you would need to convert this within the formula to the same format as appears in your diary text. I have assumed the UK format of dd mmm yyyy. In which case In C10 use =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND($B10,$A$1:$A$1000)))* (ISNUMBER(FIND(TEXT(C$9,"dd mmm yyyy"),$A$1:$A$1000)))) And why are we multiplying matrices ? That's the way Sumproduct works. Each set of tests will produce True or False Firstly for each cell in the range A1:A1000 can "rain" be found, True or False Next for each of those cells can the Town be found Then can the Date be found. So we would end up with something like T,F,T,F,F,F,F F,F,T,T,F,T,F T,F,T,F,T,F,T When we do the multiplication, the Trues are coerced to 1's and the Falses to 0's so we get 1,0,1,0,0,0,0 0,0,1,1,0,1,0 1,0,1,0,1,0,1 which when multiplied becomes 0,0,1,0,0,0,0 Which Sumproduct would then sum to 1 For more explanation on how Sumproduct works, take a look at Bob Phillips excellent treatise on the subject at http://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Zimina" wrote in message ... Sorry Roger, I don't understand the reply. What's happening in B$10 and C$10 ? And why are we multiplying matrices ? TIA Zimina "Roger Govier" a écrit dans le message de news: ... Hi Supposing the text you want to look at is all contained in column A and the value being searched "rain" is held in D1 In cell C10 =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND(B$10,$A$1:$A$1000)))* (ISNUMBER(FIND($C10,$A$1:$A$1000)))) -- Regards Roger Govier "Zimina" wrote in message ... Hi, I'm stumped on this one but I'm sure that it's doable. I have a table of data based on Towns and Dates. Let's say there are 10 Towns from B10 to B19 and Dates are from C9 to I9 The table data is text extracts from diary entries for each Date in each Town. The data may (or may not) include remarks about the weather. (Isn't this interesting!). Basically, I want to be able to count instances of the word "rain" in the data entries and display the sum of occurrences horizontally (by Town) in column A and vertically (by Date) in row 7. Thanks in advance for any advice. Zimina |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to sum multiple occurrences of a word in a table of texts
http://xldynamic.com/source/xld.SUMPRODUCT.html
Really cool page. Precisely explained. Thanks Roger. "Zimina" a écrit dans le message de news: ... Thanks Roger. Wilco. Over and out. Zimina "Roger Govier" a écrit dans le message de news: ... Hi Zimina I answered the post just before leaving to go on a trip, and as soon as I got in the car I knew I had made an some errors. Please accept my apologies Firstly the comparison should have been against C$9 as that contains the first of the Dates, and all the others are on row 9. The Relativity of row 9 should have been C$9 as the row needs to stay absolute with the column varying as it is copied across For the Towns, starting in B10 it is the B that has to stay Absolute and the row relative as it is copied down, so it should be $B10 Also, unless the dates that you have typed in row 9 are Text representations of the date, then the comparison will fail, as the dates within your "diary text" will be Text. Assuming the dates in your diary are in the form 28 Nov 2006 as a piece of text, then the values in C9 onward would need to be "28 Nov 2006" and not 28/11/2006 as a true Excel date, as that will be stored internally as serial number 39049. If you do use text dates in row 9 or the same format as you have type in the diary, then the following formula entered in C10 does work. =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND($B10,$A$1:$A$1000)))* (ISNUMBER(FIND(C$9,$A$1:$A$1000)))) Copy across through D10:I10, then copy the whole of C10:I10 down through C11:C19 If you have Excel dates in row 9, then you would need to convert this within the formula to the same format as appears in your diary text. I have assumed the UK format of dd mmm yyyy. In which case In C10 use =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND($B10,$A$1:$A$1000)))* (ISNUMBER(FIND(TEXT(C$9,"dd mmm yyyy"),$A$1:$A$1000)))) And why are we multiplying matrices ? That's the way Sumproduct works. Each set of tests will produce True or False Firstly for each cell in the range A1:A1000 can "rain" be found, True or False Next for each of those cells can the Town be found Then can the Date be found. So we would end up with something like T,F,T,F,F,F,F F,F,T,T,F,T,F T,F,T,F,T,F,T When we do the multiplication, the Trues are coerced to 1's and the Falses to 0's so we get 1,0,1,0,0,0,0 0,0,1,1,0,1,0 1,0,1,0,1,0,1 which when multiplied becomes 0,0,1,0,0,0,0 Which Sumproduct would then sum to 1 For more explanation on how Sumproduct works, take a look at Bob Phillips excellent treatise on the subject at http://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "Zimina" wrote in message ... Sorry Roger, I don't understand the reply. What's happening in B$10 and C$10 ? And why are we multiplying matrices ? TIA Zimina "Roger Govier" a écrit dans le message de news: ... Hi Supposing the text you want to look at is all contained in column A and the value being searched "rain" is held in D1 In cell C10 =SUMPRODUCT((ISNUMBER(FIND($D$1,$A1:$A1000)))* (ISNUMBER(FIND(B$10,$A$1:$A$1000)))* (ISNUMBER(FIND($C10,$A$1:$A$1000)))) -- Regards Roger Govier "Zimina" wrote in message ... Hi, I'm stumped on this one but I'm sure that it's doable. I have a table of data based on Towns and Dates. Let's say there are 10 Towns from B10 to B19 and Dates are from C9 to I9 The table data is text extracts from diary entries for each Date in each Town. The data may (or may not) include remarks about the weather. (Isn't this interesting!). Basically, I want to be able to count instances of the word "rain" in the data entries and display the sum of occurrences horizontally (by Town) in column A and vertically (by Date) in row 7. Thanks in advance for any advice. Zimina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging documents,exporting long landscape table to word with page breaks & numbers | Excel Discussion (Misc queries) | |||
Linking table in Excel to word | Links and Linking in Excel | |||
Multiple MS Access table sources for pivot table | Excel Discussion (Misc queries) | |||
Copy Word table into Excel cell by cell | Excel Discussion (Misc queries) | |||
How to convert the table in word to excel sheet? | Excel Discussion (Misc queries) |