Trying to sum multiple occurrences of a word in a table of texts

[Zimina]

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

 

[Roger Govier]

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

 

[Zimina]

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]

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

 

[Zimina]

Thanks Roger.
Wilco.
Over and out.

Zimina

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

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

 

[Zimina]

http://xldynamic.com/source/xld.SUMPRODUCT.html
Really cool page.
Precisely explained.

Thanks Roger.