Sumproduct query(I think) or array entered "with wildcard * ???"

Sorry for the delay with my posts but our network is acting
up. Here's
an array function that may work with multiple criteria:

{=SUMPRODUCT((IF(ISERROR(SEARCH(A17,A2:A6)),0,1))*(B2:B6)*(C
2:C6="a"))}

John Mulholland

-----Original Message-----
From: Mulholland, John (UK)
Sent: 13 February 2006 14:42
To: 'MS Excel General Q & A List'
Subject: RE: Sumproduct query(I think) or array entered
"with wildcard *
???"

Sumif & countif functions work with wildcards.

=SUMIF(A2:A6,A17 & "*",B2:B6)

Don't know how you'd use multiple criteria though.


John Mulholland.


-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Lemmons, Wyatt
Sent: 13 February 2006 14:15
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumproduct query(I think) or array entered
"with wildcard *
???"

               *** WARNING ***

This mail has originated outside your organization, either
from an
external partner or the Global Internet.

     Keep this in mind if you answer this message.


 This got me thinking ...  Again ...

How would one employ a wild card in SUMPRODUCT aside from
the examples
below.

Specifically, an asterisk to essentially get the equivalent
to Like in
SQL  ( i.e. Like "THHN 12 STD*" )

I don't recall seeing this here, before.  I tried
=SUMPRODUCT(($A$2:$A$6=A17&"*")*$B$2:$B$6)
with the results of zero.  (
A17 = "THHN 12 STD" )


thank you,
Wyatt Lemmons, HSG
''Don't worry about people stealing your ideas. If your
ideas are any
good, you'll have to ram them down people's throats.'' 
Howard Aiken

-----Original Message-----
From: Don Guillett [mailto:dguillett1AUSTIN.RR.COM]

Sent: Saturday, February 11, 2006 5:46 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumproduct query(I think) or array entered

or this idea. I used the same table. You would want to use
another table

with ONLY the uniques.

=SUMPRODUCT((LEFT(E3:E13,LEN(E3))=E3)*F3:F13)

Don Guillett
SalesAid Software
dguillett1austin.rr.com
----- Original Message -----

From: "Bob Phillips" <bob.phillipsDSL.PIPEX.COM>
To: <EXCEL-GPEACH.EASE.LSOFT.COM>
Sent: Saturday, February 11, 2006 3:21 AM
Subject: Re: Sumproduct query(I think) or array entered


> =SUMPRODUCT(--(LEFT(A2:A200,11)="THHN 12
STD"),B2:B200)
>
> -----Original Message-----
> From: MS Excel General Q & A List
[mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
> On Behalf Of Jim Poer
> Sent: 10 February 2006 20:41
> To: EXCEL-GPEACH.EASE.LSOFT.COM
> Subject: Sumproduct query(I think) or array entered
>
>
> I have 2 columns. ColA has catalog numbers. ColB has
quantities.
> THHN 12 STD WHITE         2000
> THHN 12 STD YELLOW       1000
> THHN 350                          500
> THHN 12 STD WHITE          2000
> THHN 12 SOL PINK             500
>
> Need a formula that will look down column A and sum the
quantities in
> Column B if the first 11 characters of each catalog
number
> match(counting spaces).
>
> Desired Result from above would be:
> THHN 12 STD        5000
> THHN 350              500
> THHN 12 SOL         500
>
> ie I don't care about the colors, I just want to know
the total
footage
> of the like type wires by size and whether they are std
or sol. Pivot
> Table stills separates them by colors. The formula I am
looking for
> could look down the pivot table or the database the
pivot table is
> working from.
>  thanks
>  Jim
> Hope that's clear as mud
>
>


************************************************************
********
This email and any attachments are confidential to the
intended
recipient and may also be privileged. If you are not the
intended
recipient please delete it from your system and notify the
sender.
You should not copy it or use it for any purpose nor
disclose or
distribute its contents to any other person.
************************************************************
********

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

And everyone forgets DFunctions! ;)

Since your data table is already set out, I'm assuming that
you've got
headers in row 1 and the data starts in row 2

So let's assume that your database is A1:B6
1) copy the header for the  code into three separate cells
(F1:H1)
2) in the cell directly below each of these, type in the
wildcard
criterion you want. Note you DO NOT need asterisks, so
typing in THHN 12
STD
 is the equivalent of THHN 12 STD*
3) somewhere in a blank cell, type in
=DSUM($A$1:$B$6,2,F1:F2)
4) repeat 3) for each criterion (G1:G2, H1:H2)

the database remains the same ($A$1:$B$6) and the column
index is also
static - it's the column you're adding up.
If you want to get an exact match, then use ="=THHN 12
STD" in the criterrion

The only downside in using Dfunctions id that you need one
criterion range
per formula, but yiu can create extremely complex multiple
criteria, AND
unlike a pivot table, any changes to the database are
reflected real-time
- no refresh needed
HTH
L




> Sorry for the delay with my posts but our network is
acting up. Here's
> an array function that may work with multiple criteria:
>
>
{=SUMPRODUCT((IF(ISERROR(SEARCH(A17,A2:A6)),0,1))*(B2:B6)*(C
2:C6="a"))}
>
> John Mulholland
>
> -----Original Message-----
> From: Mulholland, John (UK)
> Sent: 13 February 2006 14:42
> To: 'MS Excel General Q & A List'
> Subject: RE: Sumproduct query(I think) or array entered
"with wildcard *
> ???"
>
> Sumif & countif functions work with wildcards.
>
> =SUMIF(A2:A6,A17 & "*",B2:B6)
>
> Don't know how you'd use multiple criteria though.
>
>
> John Mulholland.
>
>
> -----Original Message-----
> From: MS Excel General Q & A List
[mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
> On Behalf Of Lemmons, Wyatt
> Sent: 13 February 2006 14:15
> To: EXCEL-GPEACH.EASE.LSOFT.COM
> Subject: Re: Sumproduct query(I think) or array entered
"with wildcard *
> ???"
>
>                *** WARNING ***
>
> This mail has originated outside your organization,
either from an
> external partner or the Global Internet.
>
>      Keep this in mind if you answer this message.
>
>
>  This got me thinking ...  Again ...
>
> How would one employ a wild card in SUMPRODUCT aside
from the examples
> below.
>
> Specifically, an asterisk to essentially get the
equivalent to Like in
> SQL  ( i.e. Like "THHN 12 STD*" )
>
> I don't recall seeing this here, before.  I tried
>
=SUMPRODUCT(($A$2:$A$6=A17&"*")*$B$2:$B$6)
with the results of zero.  (
> A17 = "THHN 12 STD" )
>
>
> thank you,
> Wyatt Lemmons, HSG
> ''Don't worry about people stealing your ideas. If
your ideas are any
> good, you'll have to ram them down people's
throats.''  Howard Aiken
>
> -----Original Message-----
> From: Don Guillett [mailto:dguillett1AUSTIN.RR.COM]
>
> Sent: Saturday, February 11, 2006 5:46 AM
> To: EXCEL-GPEACH.EASE.LSOFT.COM
> Subject: Re: Sumproduct query(I think) or array entered
>
> or this idea. I used the same table. You would want to
use another table
>
> with ONLY the uniques.
>
> =SUMPRODUCT((LEFT(E3:E13,LEN(E3))=E3)*F3:F13)
>
> Don Guillett
> SalesAid Software
> dguillett1austin.rr.com
> ----- Original Message -----
>
> From: "Bob Phillips" <bob.phillipsDSL.PIPEX.COM>
> To: <EXCEL-GPEACH.EASE.LSOFT.COM>
> Sent: Saturday, February 11, 2006 3:21 AM
> Subject: Re: Sumproduct query(I think) or array entered
>
>
>> =SUMPRODUCT(--(LEFT(A2:A200,11)="THHN 12
STD"),B2:B200)
>>
>> -----Original Message-----
>> From: MS Excel General Q & A List
> [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
>> On Behalf Of Jim Poer
>> Sent: 10 February 2006 20:41
>> To: EXCEL-GPEACH.EASE.LSOFT.COM
>> Subject: Sumproduct query(I think) or array entered
>>
>>
>> I have 2 columns. ColA has catalog numbers. ColB
has quantities.
>> THHN 12 STD WHITE         2000
>> THHN 12 STD YELLOW       1000
>> THHN 350                          500
>> THHN 12 STD WHITE          2000
>> THHN 12 SOL PINK             500
>>
>> Need a formula that will look down column A and sum
the quantities in
>> Column B if the first 11 characters of each catalog
number
>> match(counting spaces).
>>
>> Desired Result from above would be:
>> THHN 12 STD        5000
>> THHN 350              500
>> THHN 12 SOL         500
>>
>> ie I don't care about the colors, I just want to
know the total
> footage
>> of the like type wires by size and whether they are
std or sol. Pivot
>> Table stills separates them by colors. The formula
I am looking for
>> could look down the pivot table or the database the
pivot table is
>> working from.
>>  thanks
>>  Jim
>> Hope that's clear as mud
>>
>>
>
>
>
************************************************************
********
> This email and any attachments are confidential to the
intended
> recipient and may also be privileged. If you are not
the intended
> recipient please delete it from your system and notify
the sender.
> You should not copy it or use it for any purpose nor
disclose or
> distribute its contents to any other person.
>
************************************************************
********
>
>
------------------------------------------------------------
--------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
> international's LISTSERV(R) software.  For
subscription/signoff info
> and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

That's the one!! It turned out to be more intricate that I
first thought,
but this method solves all problems and plugs all holes.
Thanks Laurent and
all of the other great suggestions!!!!!
   Jim

-----Original Message-----
From: MS Excel General Q & A List
[mailto:EXCEL-GPEACH.EASE.LSOFT.COM]On Behalf Of Laurent
Wright
Sent: Monday, February 13, 2006 11:02 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumproduct query(I think) or array entered
'with wildcard *
???'


And everyone forgets DFunctions! ;)

Since your data table is already set out, I'm assuming that
you've got
headers in row 1 and the data starts in row 2

So let's assume that your database is A1:B6
1) copy the header for the  code into three separate cells
(F1:H1)
2) in the cell directly below each of these, type in the
wildcard
criterion you want. Note you DO NOT need asterisks, so
typing in THHN 12
STD
 is the equivalent of THHN 12 STD*
3) somewhere in a blank cell, type in
=DSUM($A$1:$B$6,2,F1:F2)
4) repeat 3) for each criterion (G1:G2, H1:H2)

the database remains the same ($A$1:$B$6) and the column
index is also
static - it's the column you're adding up.
If you want to get an exact match, then use ="=THHN 12
STD" in the
criterrion

The only downside in using Dfunctions id that you need one
criterion range
per formula, but yiu can create extremely complex multiple
criteria, AND
unlike a pivot table, any changes to the database are
reflected real-time
- no refresh needed
HTH
L




> Sorry for the delay with my posts but our network is
acting up. Here's
> an array function that may work with multiple criteria:
>
>
{=SUMPRODUCT((IF(ISERROR(SEARCH(A17,A2:A6)),0,1))*(B2:B6)*(C
2:C6="a"))}
>
> John Mulholland
>
> -----Original Message-----
> From: Mulholland, John (UK)
> Sent: 13 February 2006 14:42
> To: 'MS Excel General Q & A List'
> Subject: RE: Sumproduct query(I think) or array entered
"with wildcard *
> ???"
>
> Sumif & countif functions work with wildcards.
>
> =SUMIF(A2:A6,A17 & "*",B2:B6)
>
> Don't know how you'd use multiple criteria though.
>
>
> John Mulholland.
>
>
> -----Original Message-----
> From: MS Excel General Q & A List
[mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
> On Behalf Of Lemmons, Wyatt
> Sent: 13 February 2006 14:15
> To: EXCEL-GPEACH.EASE.LSOFT.COM
> Subject: Re: Sumproduct query(I think) or array entered
"with wildcard *
> ???"
>
>                *** WARNING ***
>
> This mail has originated outside your organization,
either from an
> external partner or the Global Internet.
>
>      Keep this in mind if you answer this message.
>
>
>  This got me thinking ...  Again ...
>
> How would one employ a wild card in SUMPRODUCT aside
from the examples
> below.
>
> Specifically, an asterisk to essentially get the
equivalent to Like in
> SQL  ( i.e. Like "THHN 12 STD*" )
>
> I don't recall seeing this here, before.  I tried
>
=SUMPRODUCT(($A$2:$A$6=A17&"*")*$B$2:$B$6)
with the results of zero.  (
> A17 = "THHN 12 STD" )
>
>
> thank you,
> Wyatt Lemmons, HSG
> ''Don't worry about people stealing your ideas. If
your ideas are any
> good, you'll have to ram them down people's
throats.''  Howard Aiken
>
> -----Original Message-----
> From: Don Guillett [mailto:dguillett1AUSTIN.RR.COM]
>
> Sent: Saturday, February 11, 2006 5:46 AM
> To: EXCEL-GPEACH.EASE.LSOFT.COM
> Subject: Re: Sumproduct query(I think) or array entered
>
> or this idea. I used the same table. You would want to
use another table
>
> with ONLY the uniques.
>
> =SUMPRODUCT((LEFT(E3:E13,LEN(E3))=E3)*F3:F13)
>
> Don Guillett
> SalesAid Software
> dguillett1austin.rr.com
> ----- Original Message -----
>
> From: "Bob Phillips" <bob.phillipsDSL.PIPEX.COM>
> To: <EXCEL-GPEACH.EASE.LSOFT.COM>
> Sent: Saturday, February 11, 2006 3:21 AM
> Subject: Re: Sumproduct query(I think) or array entered
>
>
>> =SUMPRODUCT(--(LEFT(A2:A200,11)="THHN 12
STD"),B2:B200)
>>
>> -----Original Message-----
>> From: MS Excel General Q & A List
> [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
>> On Behalf Of Jim Poer
>> Sent: 10 February 2006 20:41
>> To: EXCEL-GPEACH.EASE.LSOFT.COM
>> Subject: Sumproduct query(I think) or array entered
>>
>>
>> I have 2 columns. ColA has catalog numbers. ColB
has quantities.
>> THHN 12 STD WHITE         2000
>> THHN 12 STD YELLOW       1000
>> THHN 350                          500
>> THHN 12 STD WHITE          2000
>> THHN 12 SOL PINK             500
>>
>> Need a formula that will look down column A and sum
the quantities in
>> Column B if the first 11 characters of each catalog
number
>> match(counting spaces).
>>
>> Desired Result from above would be:
>> THHN 12 STD        5000
>> THHN 350              500
>> THHN 12 SOL         500
>>
>> ie I don't care about the colors, I just want to
know the total
> footage
>> of the like type wires by size and whether they are
std or sol. Pivot
>> Table stills separates them by colors. The formula
I am looking for
>> could look down the pivot table or the database the
pivot table is
>> working from.
>>  thanks
>>  Jim
>> Hope that's clear as mud
>>
>>
>
>
>
************************************************************
********
> This email and any attachments are confidential to the
intended
> recipient and may also be privileged. If you are not
the intended
> recipient please delete it from your system and notify
the sender.
> You should not copy it or use it for any purpose nor
disclose or
> distribute its contents to any other person.
>
************************************************************
********
>
>
------------------------------------------------------------
--------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
> international's LISTSERV(R) software.  For
subscription/signoff info
> and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G