Sumproduct and wildcards

V

vipa2000

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

Bob Phillips

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

Don Guillett

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"}
 
V

vipa2000

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

vipa2000

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

Bob Phillips

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

Bob Phillips

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

vipa2000

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*"})))
 
K

KL

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)*(YEAR(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
 
K

KL

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
Click to expand...

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
 
K

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)*(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
 
V

vipa2000

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 said:
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 said:
Correction. The following passage:


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
Click to expand...
Click to expand...
 
J

JE McGimpsey

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().
 
K

KL

Hi vipa,


vipa2000 said:
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.
Click to expand...

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?
Click to expand...

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(Sheet1!$I$2:$I$30000)=F2)*(YEAR(Sheet1!$I$2:$I$30000)=H2)*ISNUMBER(SEARCH({"*TECO*","*CLSD*"},Sheet1!$E$2:$E$30000)))

Regards,
KL
 
V

vipa2000

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

Domenic

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!

vipa2000 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count problem part 2 5
count problem 6
Search substring in a string 4
Add a sequence number(question number) to a paragraph which has a question and 4 options and answer 0
sumproduct and percentage 1
sumproduct - deduct one from another. 6
I think i need an if and maybe something else 4
SUMPRODUCT with two valid criteria 1

Top