Multiple Countif with wildcard criteria

  • Thread starter Thread starter Awrex
  • Start date Start date
A

Awrex

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!!!
 
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!!
 
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
 
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!!!!!!!!!
 
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
 
Ashish,

Its probably just a matter of interp, but I read the OP's line:
as hinting that s/he wanted an OR computation viz.:
"Sony Production" or "Aiwa Staging"
rather than singles
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
 
Yes.

I need to COUNTIF based on two criteria, which the COUNTIF function can't
do. I can't use an array with wildcard characters. I've seen SUMPRODUCT but
this doesn't work, and some examples of NUMBERIS which included a SEARCH
function as well.

The examples I have seen I have tried and I usually get a VALUE# or NUM# or
some other error that doesn't make sense. So when I do some research I find
out that the criteria usually has to be numeric and or can't use wildcards,
i.e. *, ?, etc.
 
Using your data provided:

Column A Column B
------------------- -------------------
Sony Production
Sony Staging
Aiwa Production
Sony Production
Aiwa Staging
Aiwa 8973
Sony 8963

Go back to a previous suggestion (modified for clarity)

=SUMPRODUCT((A1:A7="Sony")*(B1:B7="Production"))

will give a result of 2 and

=SUMPRODUCT((A1:A7="Aiwa")*(B1:B7="Staging"))

will give a result of 1.

Are those the results you are looking for?
 
Several possibilities. If it's always "\target\sony\..." or "\target\aiwa\...",
you could use

=SUMPRODUCT((LEFT(A1:A7,12)="\target\sony\")*(B1:B7="Production"))

If the "\target\" part could change, then you could use something like this:

=MID(A1,FIND("\",A1,2)+1,FIND("\",A1,FIND("\",A1,2)+1)-FIND("\",A1,2)-1)

in a helper column to isolate the text between the second and third
back-slashes. Then use the SUMPRODUCT() on that column.
 
Back
Top