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

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

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

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

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

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...

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

Multiple Countif with wildcard criteria
 

Ashish,

Its probably just a matter of interp, but I read the OP's line:
I need to count Sony Production or Aiwa Staging, etc...

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
---

Multiple Countif with wildcard criteria
 

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.


"Max" wrote:

Ashish,

Its probably just a matter of interp, but I read the OP's line:
I need to count Sony Production or Aiwa Staging, etc...

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
---

Multiple Countif with wildcard criteria
 

So what result did you get from Max's suggestion of =SUMPRODUCT(--(A2:A8&"
"&B2:B8={"Sony Production","Aiwa Staging"})) ?
--
David Biddulph

"Awrex" wrote in message
...
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.


"Max" wrote:

Ashish,

Its probably just a matter of interp, but I read the OP's line:
I need to count Sony Production or Aiwa Staging, etc...

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
---

Multiple Countif with wildcard criteria
 

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?


Awrex wrote:
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.


"Max" wrote:

Ashish,

Its probably just a matter of interp, but I read the OP's line:
I need to count Sony Production or Aiwa Staging, etc...
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
---

Multiple Countif with wildcard criteria
 

That works, though how would I change this if the column A data changed as
follows:


Column A Column B
------------ -------------
\target\sony\direct\ Production
\target\aiwa\sales\ Production

Forgot this in the last post, reason why I was needing the wildcard option.


"Glenn" wrote:

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?


Awrex wrote:
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.


"Max" wrote:

Ashish,

Its probably just a matter of interp, but I read the OP's line:
I need to count Sony Production or Aiwa Staging, etc...
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
---

Multiple Countif with wildcard criteria
 

Several possibilities. If it's always "\target\sony\..." or "\target\aiwa\...",
you could use

=SUMPRODUCT((LEFT(A1:A7,12)="\target\sony\")*(B1:B 7="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.



Awrex wrote:
That works, though how would I change this if the column A data changed as
follows:


Column A Column B
------------ -------------
\target\sony\direct\ Production
\target\aiwa\sales\ Production

Forgot this in the last post, reason why I was needing the wildcard option.


"Glenn" wrote:

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?


Awrex wrote:
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.


"Max" wrote:

Ashish,

Its probably just a matter of interp, but I read the OP's line:
I need to count Sony Production or Aiwa Staging, etc...
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
---