Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Criteria Reference Cell | Excel Worksheet Functions | |||
COUNTIF cell reference won't work | Excel Worksheet Functions | |||
COUNTIF less than cell reference | Excel Discussion (Misc queries) | |||
Countif with cell reference | Excel Worksheet Functions | |||
COUNTIF text where there is more than one word in a cell | Excel Worksheet Functions |