Need help with frequency formula
 

Does anyone know how to determine the most used words of numbers within a
given range. For example, I am trying to figure out the following:

A B C D F
1 dog Cat Snake Pig positive
2 Cat Snake Horse Blue positive
3 light bulb Cat foot negative
4 kid school hat Cat negative


I need a formula that would count the words in columns "A" thru "D" for
example but would only return the words based on the value of column "F,"
(i.e. -- how many times the word "Cat" appears in A:D, but negative in F.
Any help would be greatly apprieceated. Thanks in advance.

Need help with frequency formula
 

On Fri, 6 Feb 2009 23:49:01 -0800, ISAF Media Analysis
wrote:

Does anyone know how to determine the most used words of numbers within a
given range. For example, I am trying to figure out the following:

A B C D F
1 dog Cat Snake Pig positive
2 Cat Snake Horse Blue positive
3 light bulb Cat foot negative
4 kid school hat Cat negative


I need a formula that would count the words in columns "A" thru "D" for
example but would only return the words based on the value of column "F,"
(i.e. -- how many times the word "Cat" appears in A:D, but negative in F.
Any help would be greatly apprieceated. Thanks in advance.


Try this formula:

=SUM(MMULT(TRANSPOSE(--(A1:D100="Cat")),(--(F1:F100="negative"))))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Change the 100 in both places to fit the size of your data.

Hope this helps / Lars-Åke

Need help with frequency formula
 

this one

=SUMPRODUCT((A1:D10="cat")*(F1:F10="negative"))

ISAF Media Analysis wrote:

Does anyone know how to determine the most used words of numbers within a
given range. For example, I am trying to figure out the following:

A B C D F
1 dog Cat Snake Pig positive
2 Cat Snake Horse Blue positive
3 light bulb Cat foot negative
4 kid school hat Cat negative


I need a formula that would count the words in columns "A" thru "D" for
example but would only return the words based on the value of column "F,"
(i.e. -- how many times the word "Cat" appears in A:D, but negative in F.
Any help would be greatly apprieceated. Thanks in advance.