I know sumproduct won't work with wildcards, but i need to use something that
allows me to do this.

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now need to
expand the formula so that it will look for the word TECO or CLSD in a cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

try adding another parameter of
yourrange={"TECO","CLSD"}

--
Don Guillett
SalesAid Software

"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use something
that
allows me to do this.


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

Hi Don, not too sure what you mean. I can't see how this works.
--
Regards vipa


"Don Guillett" wrote:

try adding another parameter of
yourrange={"TECO","CLSD"}

--
Don Guillett
SalesAid Software

"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use something
that
allows me to do this.


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

Add this to your formula

--(COUNTIF($A$20:$A$3000,{"*TECO*","*CLSD*"}))

changing the column to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use something
that
allows me to do this.


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

I think I now see your problem. The criteria is part of a list. Is the
desired always the 1st 4. How about more examples
maybe

left(yourrange,4)={"TECO","CLSD"}


--
Don Guillett
SalesAid Software

"vipa2000" wrote in message
...
Hi Don, not too sure what you mean. I can't see how this works.
--
Regards vipa


"Don Guillett" wrote:

try adding another parameter of
yourrange={"TECO","CLSD"}

--
Don Guillett
SalesAid Software

"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use
something
that
allows me to do this.



=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now
need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

Bob, do you ever sleep? my thanks to you again. My formula is as below

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(COUNTIF($I$2:$I$30000,{"*TECO*","*CLSD*"}))

it is coming up with a value error. Checking the error it states that a
valur used in the formula is the wrong data type. I think it is just me!!!!
--
Regards vipa


"Bob Phillips" wrote:

Add this to your formula

--(COUNTIF($A$20:$A$3000,{"*TECO*","*CLSD*"}))

changing the column to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use something
that
allows me to do this.


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

bob, for my education what does the use of the $ symbols do. I have trawled
quite a few websites and the online help to no avail.

--
Regards vipa


"Bob Phillips" wrote:

Add this to your formula

--(COUNTIF($A$20:$A$3000,{"*TECO*","*CLSD*"}))

changing the column to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use something
that
allows me to do this.


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

Vipa: I'm convinced, too, that Bob never sleeps.
*******************
~Anne Troy

www.OfficeArticles.com


"vipa2000" wrote in message
...
Bob, do you ever sleep? my thanks to you again. My formula is as below


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(COUNTIF($I$2:
$I$30000,{"*TECO*","*CLSD*"}))

it is coming up with a value error. Checking the error it states that a
valur used in the formula is the wrong data type. I think it is just
me!!!!
--
Regards vipa


"Bob Phillips" wrote:

Add this to your formula

--(COUNTIF($A$20:$A$3000,{"*TECO*","*CLSD*"}))

changing the column to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use
something
that
allows me to do this.



=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now
need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

Hi Vipa,

Yeah I sleep. Just different time zones.

The TECO, CLSD values can't be in column I as we have already ascertained
that column I contains dates. I think you must mean another column.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
Bob, do you ever sleep? my thanks to you again. My formula is as below


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(COUNTIF($I$2:
$I$30000,{"*TECO*","*CLSD*"}))

it is coming up with a value error. Checking the error it states that a
valur used in the formula is the wrong data type. I think it is just
me!!!!
--
Regards vipa


"Bob Phillips" wrote:

Add this to your formula

--(COUNTIF($A$20:$A$3000,{"*TECO*","*CLSD*"}))

changing the column to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use
something
that
allows me to do this.



=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now
need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

The $ is to make that part of the cell reference (column or row) absolute.
This is so that if you copy the formula to another cell, that part of the
formula will not shift in relation to where you copy it to.

Taking a very simple example, in A1

=SUM(B1:B10)

copy that cell and paste it to say A11, you will see the formula update to
=SUM(B11:B20). But if you start with =SUM($B$1:$B$10) and copy it to A11, it
will stay as =SUM($B$1:$B$10).

In our formula, we don't want the range being checked to modify if we copy
the formula, so we lock it down with $.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
bob, for my education what does the use of the $ symbols do. I have
trawled
quite a few websites and the online help to no avail.

--
Regards vipa


"Bob Phillips" wrote:

Add this to your formula

--(COUNTIF($A$20:$A$3000,{"*TECO*","*CLSD*"}))

changing the column to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)

I think i must have reached saturation last night. Right my code is as
below. Still getting the value error problem. Column E is formatted as text.

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"})))
--
Regards vipa


"Bob Phillips" wrote:

Hi Vipa,

Yeah I sleep. Just different time zones.

The TECO, CLSD values can't be in column I as we have already ascertained
that column I contains dates. I think you must mean another column.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
Bob, do you ever sleep? my thanks to you again. My formula is as below


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(COUNTIF($I$2:
$I$30000,{"*TECO*","*CLSD*"}))

it is coming up with a value error. Checking the error it states that a
valur used in the formula is the wrong data type. I think it is just
me!!!!
--
Regards vipa


"Bob Phillips" wrote:

Add this to your formula

--(COUNTIF($A$20:$A$3000,{"*TECO*","*CLSD*"}))

changing the column to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use
something
that
allows me to do this.



=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I now
need
to
expand the formula so that it will look for the word TECO or CLSD in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

Hi vipa,

The following parts of your formula:
--(Sheet1!$C$2:$C$30000=1)
--(Sheet1!$D$2:$D$30000="")
--(MONTH(Sheet1!$I$2:$I$30000)=F2)
--(YEAR(Sheet1!$I$2:$I$30000)=H2)
return a 29999-member vertical array each

while the last part:

--(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"}))

returns a 2-member horizontal array

I guess, problem is that SUMPRODUCT can't handle it on its own and will
return error.

Now, if you substitute the five arguments of SUMPRODUCT by one, where each
part is a multiplier:

=SUMPRODUCT((Sheet1!$C$2:$C$30000=1)*(Sheet1!$D$2: $D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=F2)*(YEA R(Sheet1!$I$2:$I$30000)=H2)*(COUNTIF($E$2:$E$30000 ,{"*TECO*","*CLSD*"})))

then SUMPRODUCT will be able to digest it..., but I don't think the formula
would return the correct result. That's because the last part of it
evaluates the whole column and not each reacord in it separately. I.e. if
any of the values "*TECO*" & "*CLSD*" is present in the column it will
return 59,998 otherwise it will return 0

If I understand correctly what you are after, try this formula:

=SUMPRODUCT(--(Sheet1!$C$2:$C$30=1),--(Sheet1!$D$2:$D$30=""),--(MONTH(Sheet1!$I$2:$I$30)=F2),--(YEAR(Sheet1!$I$2:$I$30)=H2),--(ISNUMBER(SEARCH({"*TECO*";"*CLSD*"},$E$2:$E$30))) )

I guess it doesn't need to be array-entered, but haven't tested it.

If you want the test SEARCH({"*TECO*";"*CLSD*"},$E$2:$E$30) to be
case-sensitive, then replace SEARCH by FIND.

Hope this helps.

Regards,
KL



"vipa2000" wrote in message
...
I think i must have reached saturation last night. Right my code is as
below. Still getting the value error problem. Column E is formatted as
text.

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"})))
--
Regards vipa


"Bob Phillips" wrote:

Hi Vipa,

Yeah I sleep. Just different time zones.

The TECO, CLSD values can't be in column I as we have already ascertained
that column I contains dates. I think you must mean another column.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
Bob, do you ever sleep? my thanks to you again. My formula is as below


=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(COUNTIF($I$2:
$I$30000,{"*TECO*","*CLSD*"}))

it is coming up with a value error. Checking the error it states that
a
valur used in the formula is the wrong data type. I think it is just
me!!!!
--
Regards vipa


"Bob Phillips" wrote:

Add this to your formula

--(COUNTIF($A$20:$A$3000,{"*TECO*","*CLSD*"}))

changing the column to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vipa2000" wrote in message
...
I know sumproduct won't work with wildcards, but i need to use
something
that
allows me to do this.



=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(
Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2))

The above works fine thanks to a lot of help from Bob. However I
now
need
to
expand the formula so that it will look for the word TECO or CLSD
in a
cell.

The cell can contain data in this format

TECO PCNF PRT NMAT PRC SETC

I have tried a number of things to no avail. Help appreciated.

--
Regards vipa

Correction. The following passage:

any of the values "*TECO*" & "*CLSD*" is present in the column it will
return 59,998 otherwise it will return 0

should read as follows:

------------------
one of the values "*TECO*" & "*CLSD*" is present in the column it will
return the number of lines where the rest of conditions together are met

both values "*TECO*" & "*CLSD*" are present in the column it will
return twice the number of lines where the rest of conditions together are
met

none of the values "*TECO*" & "*CLSD*" is present in the column it will
return 0
------------------

Also please note that the final part of your
formula --(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"})) was making reference
to the sheet where the formula is and not to the Sheet1 as the rest of the
arguments. This may well be on purpose, but if it wasn't, my final suggested
formula should look like this:

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(ISNUMBER(SEARCH({"*TECO*";"*CLSD*"},Sheet1!$E$2:$ E$30000))))


Regards,
KL

Hmmm... This is nice - having written a large posting explaining that
SUMPRODUCT can't handle arrays that are of different dimensions, I finally
suggest a formula that does exactly that :-O

Here goes my third try (sorry for being so hasty) :

=SUMPRODUCT((Sheet1!$C$2:$C$30000=1)*(Sheet1!$D$2: $D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=F2)*(YEA R(Sheet1!$I$2:$I$30000)=H2)*ISNUMBER(SEARCH({"*TEC O*","*CLSD*"},Sheet1!$E$2:$E$30000)))

(please also note that in my previous formula I mistakenly put semi-colon
separator instead of comma in {"*TECO*","*CLSD*"})

Now, this formula will work fine as long as there are no strings in column
[E] that include both TECO and CLSD, e.g. "123TECOCLSD@", in which case it
will double count rows.

Regards,
KL


"KL" wrote in message
...
Correction. The following passage:

any of the values "*TECO*" & "*CLSD*" is present in the column it will
return 59,998 otherwise it will return 0

should read as follows:

------------------
one of the values "*TECO*" & "*CLSD*" is present in the column it will
return the number of lines where the rest of conditions together are met

both values "*TECO*" & "*CLSD*" are present in the column it will
return twice the number of lines where the rest of conditions together are
met

none of the values "*TECO*" & "*CLSD*" is present in the column it will
return 0
------------------

Also please note that the final part of your
formula --(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"})) was making
reference to the sheet where the formula is and not to the Sheet1 as the
rest of the arguments. This may well be on purpose, but if it wasn't, my
final suggested formula should look like this:

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(ISNUMBER(SEARCH({"*TECO*";"*CLSD*"},Sheet1!$E$2:$ E$30000))))


Regards,
KL

thanks KL, worked a treat. Do i need to be worried about the loss of the --
prefixes? I have read a number of articles on the importance of these. Also I
now want the code to look for type 2's and not just type 1's (first bit of
code.) If there an efficient way to do this rather than reapeating the code
and adding the two together?

=SUMPRODUCT((Sheet1!$C$2:$C$30000=1)*(Sheet1!$D$2: $D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=F2)*(YEA R(Sheet1!$I$2:$I$30000)=H2)*ISNUMBER(SEARCH({"*TEC O*","*CLSD*"},Sheet1!$E$2:$E$30000)))

--
Regards vipa


"KL" wrote:

Hmmm... This is nice - having written a large posting explaining that
SUMPRODUCT can't handle arrays that are of different dimensions, I finally
suggest a formula that does exactly that :-O

Here goes my third try (sorry for being so hasty) :

=SUMPRODUCT((Sheet1!$C$2:$C$30000=1)*(Sheet1!$D$2: $D$30000="")*(MONTH(Sheet1!$I$2:$I$30000)=F2)*(YEA R(Sheet1!$I$2:$I$30000)=H2)*ISNUMBER(SEARCH({"*TEC O*","*CLSD*"},Sheet1!$E$2:$E$30000)))

(please also note that in my previous formula I mistakenly put semi-colon
separator instead of comma in {"*TECO*","*CLSD*"})

Now, this formula will work fine as long as there are no strings in column
[E] that include both TECO and CLSD, e.g. "123TECOCLSD@", in which case it
will double count rows.

Regards,
KL


"KL" wrote in message
...
Correction. The following passage:

any of the values "*TECO*" & "*CLSD*" is present in the column it will
return 59,998 otherwise it will return 0

should read as follows:

------------------
one of the values "*TECO*" & "*CLSD*" is present in the column it will
return the number of lines where the rest of conditions together are met

both values "*TECO*" & "*CLSD*" are present in the column it will
return twice the number of lines where the rest of conditions together are
met

none of the values "*TECO*" & "*CLSD*" is present in the column it will
return 0
------------------

Also please note that the final part of your
formula --(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"})) was making
reference to the sheet where the formula is and not to the Sheet1 as the
rest of the arguments. This may well be on purpose, but if it wasn't, my
final suggested formula should look like this:

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONTH(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(ISNUMBER(SEARCH({"*TECO*";"*CLSD*"},Sheet1!$E$2:$ E$30000))))


Regards,
KL

The importance of the double unary minuses is simply an efficient way to
convert a boolean array to a numeric array that SUMPRODUCT will work
with (it will treat booleans as 0).

Multiplying the arrays first before passing the result to SUMPRODUCT
also coerces the arrays to numeric, though at a small loss of efficiency
(presumably SUMPRODUCT's internal array multiplication is faster than
multiplying the arrays first, then passing that array).

SUMPRODUCT cannot multiply arrays of different dimensions, so if you
want to sum such a result, you have to first do the multiplication, then
send the result to SUMPRODUCT().




In article ,
"vipa2000" wrote:

Do i need to be worried about the loss of the --
prefixes? I have read a number of articles on the importance of these.

Hi vipa,


"vipa2000" wrote in message
...
thanks KL, worked a treat. Do i need to be worried about the loss of
the --
prefixes? I have read a number of articles on the importance of these.

See JE McGimpsey's post - pretty much what I would say too.

... want the code to look for type 2's and not just type 1's (first bit of
code.) If there an efficient way to do this rather than reapeating the
code
and adding the two together?

Not sure about efficiency - this type of formulae are highly "expensive"
especially when dealing with 30,000-row range and/or replicated in various
cells, but you could try this one:

=SUMPRODUCT(((Sheet1!$C$2:$C$30000=1)+(Sheet1!$C$2 :$C$30000=2)0)*(Sheet1!$D$2:$D$30000="")*(MONTH(S heet1!$I$2:$I$30000)=F2)*(YEAR(Sheet1!$I$2:$I$3000 0)=H2)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1! $E$2:$E$30000)))

Regards,
KL

Hi KL. Thank you very much. Just tried the code and then ratified it with
100% success
--
Regards vipa


"KL" wrote:

Hi vipa,


"vipa2000" wrote in message
...
thanks KL, worked a treat. Do i need to be worried about the loss of
the --
prefixes? I have read a number of articles on the importance of these.

See JE McGimpsey's post - pretty much what I would say too.

... want the code to look for type 2's and not just type 1's (first bit of
code.) If there an efficient way to do this rather than reapeating the
code
and adding the two together?

Not sure about efficiency - this type of formulae are highly "expensive"
especially when dealing with 30,000-row range and/or replicated in various
cells, but you could try this one:

=SUMPRODUCT(((Sheet1!$C$2:$C$30000=1)+(Sheet1!$C$2 :$C$30000=2)0)*(Sheet1!$D$2:$D$30000="")*(MONTH(S heet1!$I$2:$I$30000)=F2)*(YEAR(Sheet1!$I$2:$I$3000 0)=H2)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1! $E$2:$E$30000)))

Regards,
KL

I'm not sure if this is more efficient, but here's another way...

=SUMPRODUCT(ISNUMBER(MATCH(Sheet1!$C$2:$C$30000,{1 ,2},0))*(Sheet1!$D$2:$D
$30000="")*(Sheet1!$I$2:$I$30000-DAY(Sheet1!$I$2:$I$30000)+1=DATE(H2,F2,1
))*ISNUMBER(SEARCH({"TECO","CLSD"},Sheet1!$E$2:$E$ 30000)))

Hope this helps!

In article ,
"vipa2000" wrote:

thanks KL, worked a treat. Do i need to be worried about the loss of the --
prefixes? I have read a number of articles on the importance of these. Also I
now want the code to look for type 2's and not just type 1's (first bit of
code.) If there an efficient way to do this rather than reapeating the code
and adding the two together?

=SUMPRODUCT((Sheet1!$C$2:$C$30000=1)*(Sheet1!$D$2: $D$30000="")*(MONTH(Sheet1!$
I$2:$I$30000)=F2)*(YEAR(Sheet1!$I$2:$I$30000)=H2)* ISNUMBER(SEARCH({"*TECO*","*
CLSD*"},Sheet1!$E$2:$E$30000)))

--
Regards vipa


"KL" wrote:

Hmmm... This is nice - having written a large posting explaining that
SUMPRODUCT can't handle arrays that are of different dimensions, I finally
suggest a formula that does exactly that :-O

Here goes my third try (sorry for being so hasty) :

=SUMPRODUCT((Sheet1!$C$2:$C$30000=1)*(Sheet1!$D$2: $D$30000="")*(MONTH(Sheet1
!$I$2:$I$30000)=F2)*(YEAR(Sheet1!$I$2:$I$30000)=H2 )*ISNUMBER(SEARCH({"*TECO*
","*CLSD*"},Sheet1!$E$2:$E$30000)))

(please also note that in my previous formula I mistakenly put semi-colon
separator instead of comma in {"*TECO*","*CLSD*"})

Now, this formula will work fine as long as there are no strings in column
[E] that include both TECO and CLSD, e.g. "123TECOCLSD@", in which case it
will double count rows.

Regards,
KL


"KL" wrote in message
...
Correction. The following passage:

any of the values "*TECO*" & "*CLSD*" is present in the column it will
return 59,998 otherwise it will return 0

should read as follows:

------------------
one of the values "*TECO*" & "*CLSD*" is present in the column it will
return the number of lines where the rest of conditions together are met

both values "*TECO*" & "*CLSD*" are present in the column it will
return twice the number of lines where the rest of conditions together
are
met

none of the values "*TECO*" & "*CLSD*" is present in the column it will
return 0
------------------

Also please note that the final part of your
formula --(COUNTIF($E$2:$E$30000,{"*TECO*","*CLSD*"})) was making
reference to the sheet where the formula is and not to the Sheet1 as the
rest of the arguments. This may well be on purpose, but if it wasn't, my
final suggested formula should look like this:

=SUMPRODUCT(--(Sheet1!$C$2:$C$30000=1),--(Sheet1!$D$2:$D$30000=""),--(MONT
H(Sheet1!$I$2:$I$30000)=F2),--(YEAR(Sheet1!$I$2:$I$30000)=H2),--(ISNUMBER(
SEARCH({"*TECO*";"*CLSD*"},Sheet1!$E$2:$E$30000))) )


Regards,
KL