Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using "*xxx*" in an array formula
Hi everyone, Quick question. I have this array formula: {=SUM(IF(Sheet1!A1:A6="Jim",IF(Sheet1!B1:B6="Phone ",1,0),0))} And I'll quickly show you what the chart looks like: A B Jim Breather Phone Bill Fax Jim Beezley Phone John Phone Bill Car Jim Looser Car Now what I want to do is count how many times a person named "Jim" sold a phone. Its easy to do if all you have entered is "Jim", but I want it to find the substring "jim" and then count it. If there was only 1 criteria I would use COUNTIF like this: =COUNTIF(A1:A6,"*jim*") But in an array function (for multiple criteria) the little * don't work. They actually count as part of the text. Any help would be just great. -- man4mac ------------------------------------------------------------------------ man4mac's Profile: http://www.excelforum.com/member.php...o&userid=36535 View this thread: http://www.excelforum.com/showthread...hreadid=562899 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using "*xxx*" in an array formula
anyone? sorry normally I wouldn't bump my post, but I really need this for work. -- man4mac ------------------------------------------------------------------------ man4mac's Profile: http://www.excelforum.com/member.php...o&userid=36535 View this thread: http://www.excelforum.com/showthread...hreadid=562899 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using "*xxx*" in an array formula
Try: =SUMPRODUCT(--ISNUMBER(SEARCH("jim",A1:A6)),--(B1:B6="Phone")) -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562899 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using "*xxx*" in an array formula
Hi. I don't know if this is what you want but you can: Write "jim" on cell. let's say B2 and re-write your formula to: =COUNTIF(A1:A6,"B6") You can put anything into B6, once at a time, for shure... -- oteixeira ------------------------------------------------------------------------ oteixeira's Profile: http://www.excelforum.com/member.php...o&userid=35320 View this thread: http://www.excelforum.com/showthread...hreadid=562899 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using "*xxx*" in an array formula
oteixeira Wrote: Hi. I don't know if this is what you want but you can: Write "jim" on cell. let's say B2 and re-write your formula to: =COUNTIF(A1:A6,"B6") You can put anything into B6, once at a time, for shure... Thanks, but I need it to check 2 criteria at a time, and be totaly dynamic (I don't have to do anything) -- man4mac ------------------------------------------------------------------------ man4mac's Profile: http://www.excelforum.com/member.php...o&userid=36535 View this thread: http://www.excelforum.com/showthread...hreadid=562899 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using "*xxx*" in an array formula
Did you try my solution? -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562899 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using "*xxx*" in an array formula
a7n9 Wrote: Try: =SUMPRODUCT(--ISNUMBER(SEARCH("jim",A1:A6)),--(B1:B6="Phone")) Thanks I think this works! How does it work though, I can't find ISNUMBER in the excel reference. Whats going on here? -- man4mac ------------------------------------------------------------------------ man4mac's Profile: http://www.excelforum.com/member.php...o&userid=36535 View this thread: http://www.excelforum.com/showthread...hreadid=562899 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using "*xxx*" in an array formula
=SUMPRODUCT(--ISNUMBER(SEARCH("jim",A1:A6)),--(B1:B6="Phone")) Let's break down in to pieces; B1:B6="Phone" would create an array of TRUE and FALSE according to the values in the range B1:B6. By coercing it by double negation (--), we'll get an array of 0s and 1s. SEARCH("jim",A1:A6) would create an array of the positions where it found jim in the range A1:A6, if it didn't find it would return a VALUE error, therefore, we check it by ISNUMBER function if the returned value is a number or not, which would create an array of TRUE and FALSE and again using double negation (--), we'll get and array of 0s and 1s. SUMPRODUCT will just giving the summation of the product of these two arrays. Excel help should provide info on ISNUMBER function. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562899 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |