Countif contains a word in a reference cell
 

Good morning,


I have a range of information that contains text and 0,1's. The text will
change each report. In performing a summary, I want the formula to look at a
reference cell and tell me if the text *contains* the word in my reference
cell. For example,

Where Sheet 1 is a summary page and Sheet 2 contains the data (text and
0's/1's).

A B
1 Summary
2 Samsung 2
3 Micron 1
4 IBM 0
Sheet 1

A B
1 IBM Fishkill, NY 0
2 IBM 0
3 Samsung Austin 1
4 Micron 0
5 Samsung 1
6 Samsung 0
7 Micron 1
8 Samsung 0
9 Micron 0
10 Micron 0
Sheet 2


I was attempting with the following formula:

=IF(COUNTIFS(Sheet2!B1:B10,0,Sheet2!A1:A10,Sheet1! A2)=0,"",COUNTIFS(Sheet2!B1:B10,0,Sheet2!A1:A10,Sh eet1!A2))

Forumula currently only detects an exact match with contents of Sheet1!A2,
e.g. "samsung". I need it to return the count if it contains samsung. Result
should be 2.

Thanks,

Countif contains a word in a reference cell
 

In Sheet1,
In B2:
=SUMPRODUCT(ISNUMBER(SEARCH(A2,Sheet2!A$1:A$10))*( Sheet2!B$1:B$10=1))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---


"Go Bucks!!!" wrote:


Good morning,


I have a range of information that contains text and 0,1's. The text will
change each report. In performing a summary, I want the formula to look at a
reference cell and tell me if the text *contains* the word in my reference
cell. For example,

Where Sheet 1 is a summary page and Sheet 2 contains the data (text and
0's/1's).

A B
1 Summary
2 Samsung 2
3 Micron 1
4 IBM 0
Sheet 1

A B
1 IBM Fishkill, NY 0
2 IBM 0
3 Samsung Austin 1
4 Micron 0
5 Samsung 1
6 Samsung 0
7 Micron 1
8 Samsung 0
9 Micron 0
10 Micron 0
Sheet 2


I was attempting with the following formula:

=IF(COUNTIFS(Sheet2!B1:B10,0,Sheet2!A1:A10,Sheet1! A2)=0,"",COUNTIFS(Sheet2!B1:B10,0,Sheet2!A1:A10,Sh eet1!A2))

Forumula currently only detects an exact match with contents of Sheet1!A2,
e.g. "samsung". I need it to return the count if it contains samsung. Result
should be 2.

Thanks,

Countif contains a word in a reference cell
 

Almost the same formula I gave you in your original post:

=SUMPRODUCT((ISNUMBER(SEARCH(A2,Sheet2!A$1:A$20))) *(Sheet2!B$1:B$20=1))

Copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Go Bucks!!!" wrote in message
...

Good morning,


I have a range of information that contains text and 0,1's. The text will
change each report. In performing a summary, I want the formula to look at
a
reference cell and tell me if the text *contains* the word in my reference
cell. For example,

Where Sheet 1 is a summary page and Sheet 2 contains the data (text and
0's/1's).

A B
1 Summary
2 Samsung 2
3 Micron 1
4 IBM 0
Sheet 1

A B
1 IBM Fishkill, NY 0
2 IBM 0
3 Samsung Austin 1
4 Micron 0
5 Samsung 1
6 Samsung 0
7 Micron 1
8 Samsung 0
9 Micron 0
10 Micron 0
Sheet 2


I was attempting with the following formula:

=IF(COUNTIFS(Sheet2!B1:B10,0,Sheet2!A1:A10,Sheet1! A2)=0,"",COUNTIFS(Sheet2!B1:B10,0,Sheet2!A1:A10,Sh eet1!A2))

Forumula currently only detects an exact match with contents of Sheet1!A2,
e.g. "samsung". I need it to return the count if it contains samsung.
Result
should be 2.

Thanks,

Countif contains a word in a reference cell
 

You can use wildcard characters, so instead of:

Sheet1!A2

you can put:

"*"&Sheet1!A2&"*"

twice in your formula.

Hope this helps.

Pete

On Jul 28, 2:56*pm, Go Bucks!!!
wrote:
Good morning,

I have a range of information that contains text and 0,1's. *The text will
change each report. *In performing a summary, I want the formula to look at a
reference cell and tell me if the text *contains* the word in my reference
cell. *For example,

Where Sheet 1 is a summary page and Sheet 2 contains the data (text and
0's/1's). *

* * * * A * * * B
1 * * * Summary
2 * * * Samsung 2
3 * * * Micron *1
4 * * * IBM * * 0
* * * * * * * * Sheet 1

* * * * A * * * * * * * * * * * * B
1 * * * IBM Fishkill, NY * * * * * *0
2 * * * IBM * * * * * * * * * * * 0
3 * * * Samsung Austin * * * * 1
4 * * * Micron * * * * * * * * * *0
5 * * * Samsung * * * * * * * * * 1
6 * * * Samsung * * * * * * * * * 0
7 * * * Micron * * * * * * * * * *1
8 * * * Samsung * * * * * * * * * 0
9 * * * Micron * * * * * * * * * *0
10 * * *Micron * * * * * * * * * *0
* * * * * * * * Sheet 2

I was attempting with the following formula:

=IF(COUNTIFS(Sheet2!B1:B10,0,Sheet2!A1:A10,Sheet1! A2)=0,"",COUNTIFS(Sheet2!*B1:B10,0,Sheet2!A1:A10,S heet1!A2))

Forumula currently only detects an exact match with contents of Sheet1!A2,
e.g. "samsung". I need it to return the count if it contains samsung. *Result
should be 2.

Thanks,