Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
vipa2000
 
Posts: n/a
Default Sumproduct and wildcards

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
  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

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



  #6   Report Post  
vipa2000
 
Posts: n/a
Default

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




  #7   Report Post  
vipa2000
 
Posts: n/a
Default

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




  #8   Report Post  
Anne Troy
 
Posts: n/a
Default

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






  #9   Report Post  
Bob Phillips
 
Posts: n/a
Default

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






  #10   Report Post  
Bob Phillips
 
Posts: n/a
Default

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)





  #11   Report Post  
vipa2000
 
Posts: n/a
Default

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






  #12   Report Post  
KL
 
Posts: n/a
Default

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








  #13   Report Post  
KL
 
Posts: n/a
Default

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


  #14   Report Post  
KL
 
Posts: n/a
Default

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



  #15   Report Post  
vipa2000
 
Posts: n/a
Default

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






  #16   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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.

  #17   Report Post  
KL
 
Posts: n/a
Default

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


  #18   Report Post  
vipa2000
 
Posts: n/a
Default

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



  #19   Report Post  
Domenic
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can wildcards be used in SUMPRODUCT conditions Reed Excel Worksheet Functions 4 June 13th 05 10:06 PM
Wildcards with SumProduct pomalley Excel Worksheet Functions 7 March 24th 05 04:01 PM


All times are GMT +1. The time now is 11:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"