ExcelBanter - 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