Count occurences in each row.

[IllIll]

I'm trying to figure out a formula which will count the co-occurrence of 2 values in one row from multiple columns.

I need to know for instance how often the name Fred and Wilma occur in the same row.

http://i.imgur.com/N9k4iL7.png

The image is just an example, in reality there are going to be 5 different columns and around 500 rows..
The formula would need to check each row separately and if "Fred" or "Wilma" occurs add 1 to the total count..

I have to admit I'm a newbie to this stuff and just really somebody to put this puzzle together. Any help would greatly be appreciated. I've tried asking on www.reddit.com/r/excel/ but I haven't gotten anywhere yet

Please help me! lol

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F	G
1	------	------	------	------	------	------	------
2	Bob	Tom	Lisa	Tammy	Wilma		2
3	Fred	Wilma	Amy	Ted	Doofus
4	Carl	Fred	Tim	Betty	Cindy
5	Wilma	Biff	Jeff	Don	Fred

This formula entered in G2:

=SUMPRODUCT(--(MMULT((A2:E5="Fred")+(A2:E5="Wilma"),{1;1;1;1;1})=2))

Assumes each name will not be duplicated on any row.

The array {1;1;1;1;1} represents the number of columns you have.

[IllIll]

Try this...

Data Range

	A	B	C	D	E	F	G
1	------	------	------	------	------	------	------
2	Bob	Tom	Lisa	Tammy	Wilma		2
3	Fred	Wilma	Amy	Ted	Doofus
4	Carl	Fred	Tim	Betty	Cindy
5	Wilma	Biff	Jeff	Don	Fred

This formula entered in G2:

=SUMPRODUCT(--(MMULT((A2:E5="Fred")+(A2:E5="Wilma"),{1;1;1;1;1})=2))

Assumes each name will not be duplicated on any row.

The array {1;1;1;1;1} represents the number of columns you have.

Thanks man, that done the trick entirely!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[vlady]

FORUM MODERATOR'S REQUEST:

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.