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

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

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

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

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

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

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

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

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

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

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

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

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