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