Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Countif with wildcard criteria
So I've looked this up and found the following...
- multiple criteria with COUNTIF can't be used - can't use wildcards in an array - not sure if the nested ISNUMBER(SEARCH function is what I need to use I have 2 columns a of data that I need to find the total number of x in column A if x exists in column B. Column A contains folder paths as data Column B contains a mix of numbers & text Thanks ahead!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Countif with wildcard criteria
In C2:
=IF(B2="","",SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A$2:A$100))))) Copy down to last row of data in col B -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Awrex" wrote: So I've looked this up and found the following... - multiple criteria with COUNTIF can't be used - can't use wildcards in an array - not sure if the nested ISNUMBER(SEARCH function is what I need to use I have 2 columns of data that I need to find the total number of x in column A if x exists in column B. Column A contains folder paths as data Column B contains a mix of numbers & text |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Countif with wildcard criteria
Hi Max,
I'm not certain how this will compare column A to column B if X exists and give me a total count? Thanks!! "Max" wrote: In C2: =IF(B2="","",SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A$2:A$100))))) Copy down to last row of data in col B -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Awrex" wrote: So I've looked this up and found the following... - multiple criteria with COUNTIF can't be used - can't use wildcards in an array - not sure if the nested ISNUMBER(SEARCH function is what I need to use I have 2 columns of data that I need to find the total number of x in column A if x exists in column B. Column A contains folder paths as data Column B contains a mix of numbers & text |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Countif with wildcard criteria
The earlier gives you the wildcard count for each item in col B which is
found within col A, which is what I thought you wanted, going by your orig. post descriptives. If the interp's out, maybe you could clarify by posting your sample data in both cols A and B, and your expected results in col C. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Awrex" wrote: Hi Max, I'm not certain how this will compare column A to column B if X exists and give me a total count? Thanks!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Countif with wildcard criteria
Let's see if I can be clearer...
I have data in column A and data in column B. Column A Column B ------------------- ------------------- Sony Production Sony Staging Aiwa Production Sony Production Aiwa Staging Aiwa 8973 Sony 8963 I need to count Sony Production or Aiwa Staging, etc... Thanks again!!!!!!!!! "Max" wrote: The earlier gives you the wildcard count for each item in col B which is found within col A, which is what I thought you wanted, going by your orig. post descriptives. If the interp's out, maybe you could clarify by posting your sample data in both cols A and B, and your expected results in col C. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Awrex" wrote: Hi Max, I'm not certain how this will compare column A to column B if X exists and give me a total count? Thanks!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Countif with wildcard criteria
Try:
=SUMPRODUCT(--(A2:A8&" "&B2:B8={"Sony Production","Aiwa Staging"})) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Awrex" wrote: Let's see if I can be clearer... I have data in column A and data in column B. Column A Column B ------------------- ------------------- Sony Production Sony Staging Aiwa Production Sony Production Aiwa Staging Aiwa 8973 Sony 8963 I need to count Sony Production or Aiwa Staging, etc... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Countif with wildcard criteria
Hi,
Suppose you have typed Sony in cell A9 and Production in cell B9. You can now use the following formula =sumproduct((A1:A7=A18)*(B1:B7=B18)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Awrex" wrote in message ... Let's see if I can be clearer... I have data in column A and data in column B. Column A Column B ------------------- ------------------- Sony Production Sony Staging Aiwa Production Sony Production Aiwa Staging Aiwa 8973 Sony 8963 I need to count Sony Production or Aiwa Staging, etc... Thanks again!!!!!!!!! "Max" wrote: The earlier gives you the wildcard count for each item in col B which is found within col A, which is what I thought you wanted, going by your orig. post descriptives. If the interp's out, maybe you could clarify by posting your sample data in both cols A and B, and your expected results in col C. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Awrex" wrote: Hi Max, I'm not certain how this will compare column A to column B if X exists and give me a total count? Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
COUNTIF using multiple criteria | Excel Discussion (Misc queries) | |||
COUNTIF() with multiple criteria | Excel Discussion (Misc queries) | |||
Help please - Countif with multiple criteria | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |