Need help with frequency formula

[ISAF Media Analysis]

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, but negative in F.
Any help would be greatly apprieceated. Thanks in advance.

 

[Lars-Åke Aspelin]

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, but negative in F.
Any help would be greatly apprieceated. Thanks in advance.

Try this formula:

=SUM(MMULT(TRANSPOSE(--(A1100="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

 

[muddan madhu]

this one

=SUMPRODUCT((A110="cat")*(F1:F10="negative"))