[beinw]

Is it possible to build a formula in Excel 2007 that looks in one column for a particular word (i.e. "shoes") and, when it finds that word, looks in another column for a different word (i.e. "socks") that occurs in the same row as the word in the previous column and, when it finds that word ("socks"), have it count the instances where that word in the second column occurs in the same row as the other word ("shoes") did in the first column? If so, what would that formula look like?

[etaf]

you could put a formula in a column as follows

=IF( A1="shoes", COUNTIF(A1:A??,"socks"), 0)

[beinw]

I think you misunderstood my question. I need to find a word in one column, find a different word in a different column that is in the same row as the word found in the first column and then return the total number of times the second word appears in the same row as the first word. I.e., if "shoes" appears in a cell column A, I want Excel to tell me how many times the word "socks" appears in a cell in column B in the same row that "shoes" appears in column A.

A B
1 shoes bird
2 tree socks
3 bus car
4 shoes socks
5 shoes cheese
6 animal infer

The solution in this case would be "1" since "socks" appears in a cell in column B only one time next to a cell in column A where "shoes" appears.

[etaf]

are you doing this for a specific match of groups - ie Shoes and Socks , any others
or how do you decide on the criteria -
Ok I think I'm getting it having re-read
No criteria - just matching pairs and counting ??

[beinw]

etaf, those are just examples. I need an exact match of words. I have multiple words in one column but need to locate only one particular word, and different words in another column. Please look at the attachment for a sampling of entries and criteria. Does that help clarify what I'm looking for?

[etaf]

not a particular elegant answer - sure there are much better ways - just looking at match ( and maybe doing something with arrays or VB)

But a slution attached

Concatenate the cells and then use count if

[beinw]

etaf, looks good but what if the area expands or contracts based on added or deleted rows? I think we need a formula that adjusts for that. I frequently add and delete rows to my workbook. What do you think?

[etaf]

yep,
you may be able to use a list or in the concatenate and range calcs - just expand to a length you may not exceed

That will work -

I'll try the list and post

[etaf]

OK - so i have used a list function - and added the concatenate into that list
Data
List

Now if you add entries into A and B - it will also calculate in C
and change the range in all the calcs

copy and past additional lines at the * and you will see the list extend column C also extend and calcs
and the formulas counts in F also extend

if you delete rows in the list the same happens

[wedonttakecrap]

I couldn't resist finding an array solution for this problem. (Mostly because I'm secretly punishing myself)

Array formulas have the quirk of having to be entered by pressing Ctrl + Shift + Enter all together once you have the formula worked out in the formula bar.

The basic formula is below. For an in depth- explanation, check out this knowledgebase series.


=SUM(1*((A1:A23="ENROLLED")*(B1:B23="HERE")))
paste that into the formula bar and press Ctrl + Shift + Enter. Excel will then wrap the formula in {curly braces like these}.

You can add more columns by adding another *(array="string") to the others. Use +(array="string") for an OR condition instead of * for an AND.

happy hacking.

[etaf]

I knew arrays would help
But also need a variable range
Can that work on a list range

[wedonttakecrap]

I just tested the list range, and yes, it works like a charm.

When you say you need a variable range, do you mean that there will be varying columns, or just the row numbers?

Also, are you only going to have the same critera, (ie. "Enrolled, Here, Not Enrolled, There")? Or are you looking to get every unique value from the list, and every possible combination? That would be quite the undertaking.

[Zack Barresse]

Non-array entered...

=SUMPRODUCT(($A$2:A23=E9)*($B$2:B23=F9))

Non-array entered last row not known...

=SUMPRODUCT(($A$2:INDEX(A:A,MATCH(REPT("z",255),A:A),1)=E15)*($B$2:INDEX(B:B,MATCH(REPT("z",255),B:B))=F15))

These examples split the words up individually. I'll attach the sample file (I borrowed etaf's).

HTH

[wedonttakecrap]

</headsmack>
I can't believe I forgot SUMPRODUCT(). That paired with a LIST would probably be the simplest solution.

I tip my hat to you, ZB. Good show.

[etaf]

not a particular elegant answer - sure there are much better ways

Yep, was sure there was a better way
as always Zack