Find a value in a cell and check values next to it

[Macks]

Hello.

I'm looking for a function that counts the number of occurrances of values in cells next to eachother in two columns.

For example, say I want to search for the following strings randomly placed in the range A1:B30:

"hello"
"good bye"
"farewell"
"good night"
"good morning"

Say I want to search for "hello" in that range and if found in either column A or B it checks the cell next to it in the other column.
Then I need to count how many times each combination occurs in another column D and in seperate cells, kinda like this below:

Column C Column D
Word Combination Occured
"hello"+"good bye" 5
"hello"+"farewell" 3
...
"farewell"+"good bye" 3
...
etc

Appreciate any help.

[udprocks]

Hi,

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.

[Macks]

Added a sample of what I need for my worksheet.

[udprocks]

Hi,

As per my understanding, i have done with 2 helper column,,, looking for like this?

see the file plz.

[samba_ravi]

Hi Macks,
Welcome to the forum,
Enter result manually for 2 or 3 items
if possible give more details about the requirement

[Macks]

That doesn't seem to work correctly. I type "orange" and "keyboard" in all 10 rows and it still is only 1 count.

[samba_ravi]

=COUNTIFS($A$3:$A$12,$C3,$B$3:$B$12,$D3)
Try this in E3 and copy towards down

[Macks]

Exactly this is what I have:

A field of 2 columns (D3:E100) with randomly placed words.
Three more columns F, G and H where F and G should contain the word combination and H the number of occurance of the word combination.

What I need is:

In the cell next to a word combination (say "orange" and "keyboard") I need something that searches for "orange" in the range D3:E100 and checks if "keyboard" is found in the cell next to it. (strings need to work)
What should be displayed in the cell is the count of the occurance of a specific word combination.

[Macks]

=COUNTIFS($A$3:$A$12,$C3,$B$3:$B$12,$D3)
Try this in E3 and copy towards down

That doesn't work exactly as required. What I need is something that searches for a word combination in a range and the word to search for can be either in the first or second column. This assumes that the word is in the first column.

[SDCh]

Like this?

Please Login or Register  to view this content.

[samba_ravi]

=COUNTIFS($D$3:$D$100,$F3,$E$3:$E$100,$G3)+COUNTIFS($D$3:$D$100,$G3,$E$3:$E$100,$F3)
Try this
if it is not what you are looking for attach sample file with desired result

[Macks]

Thanks guys. I solved it with a helper column and the following formula in the cell:

=(COUNTIF(G$3:$G$24;CONCATENATE(C3;" ";D3)))+(COUNTIF($G$3:$G$24;CONCATENATE(D3;" ";C3)))

See final_sample.xlsx