Conditional counting of a string in 2 columns

[Exboy]

Hi!

I've searched the internet to answer my question but unfortunately had no luck. Maybe you can help me find a solution to my task.

I know how to count how many times a string appears in a column:
=(SUM(LEN(A1:A40))-SUM(LEN(SUBSTITUTE(A1:A40,"search string 1",))))/LEN("search string 1")

But I need to count only if other column (eg. B) contains "search string 2". For example I want to know how many (green, red, golden) "APPLES" were sold to (Miss, missis, mister) "BROWN".

I had a vague idea about using an asterisk for joining two "SUM" formulae. But I couldn't do it without getting an error.

Is it possible? I'm sure there is some way.

Can you please give me a hint how to do it?

Thanks in advance.

[ron2k_1]

would something like this work, given that you have xl2007:
=COUNTIFS(A:A,"*apple*",B:B,"*brown*")

[Exboy]

Thanks, but countif counts only cells that 'contain' a string, I have many "apples" and "browns" in a single cell, I need a string search.

[ron2k_1]

Give us a dummy workbook with possible cases on a range of cells and then show us what results you want.

[Exboy]

Have no idea what a dummy workbook is, but here is a screenshot:

\1

[ChemistB]

Try this array function

=SUM(IF(ISNUMBER(SEARCH(C2,B1:B40)),(LEN(A1:A40)-LEN(SUBSTITUTE(A1:A40,C1,""))),0))/LEN(C1)
where C1 contains apple and C2 contains Brown
Does that work for you? I get 4.

[Marcol]

Try this, given you are using 2007

Please Login or Register  to view this content.

Or, a more generic version using drop-downs

Please Login or Register  to view this content.

A dummy workbook is just a sample of your workbook, reduced in size to show typical details, and with any senstitve data disguised. Usually a before and after scenario is useful

A picture rarely gets an enthusiastic response.

See the Attached

[Exboy]

ChemistB & Marcol thank you very much.

[Exboy]

Thanks!

But, when I entered other data, your formula stopped giving correct results. How do I change it?

[Marcol]

Why is your data as it is?
This is nigh impossible to process as you want, and even if it can be solved, why make life hard?

It makes much more sense to have each "order" in a seperate line. not concatenated in individual cells.

What does your real data look like?

[Marcol]

I can't see how to do what you are asking for without using VBa

Try this UDF

Please Login or Register  to view this content.

When using this function specify the range by the first column only
e.g.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

This is entirely dependent on the adjacent cells in the list having the same number of lines (Alt+Enter) i.e. Chr(10) is used to split the data.

Hope this helps.

[Exboy]

Why is your data as it is?

It makes much more sense to have each "order" in a seperate line. not concatenated in individual cells.

The problem is, it's the way our .xls file is organized at work and I have no authority to change it.

P.S. Thanks, your script works!