right
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED OR
WRITTEN BY KPMG TO BE USED, AND CANNOT BE USED, BY A CLIENT
OR ANY OTHER PERSON OR ENTITY FOR THE PURPOSE OF (i)
AVOIDING PENALTIES THAT MAY BE IMPOSED ON ANY TAXPAYER OR
(ii) PROMOTING, MARKETING OR RECOMMENDING TO ANOTHER PARTY
ANY MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions specifically set forth herein and is based on
the completeness and accuracy of the stated facts,
assumptions and/or representations included. In rendering
our advice, we may consider tax authorities that are subject
to change, retroactively and/or prospectively, and any such
changes could affect the validity of our advice. We will not
update our advice for subsequent changes or modifications to
the law and regulations, or to the judicial and
administrative interpretations thereof.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-G PEACH.EASE.LSOFT.COM]
On Behalf Of Lemmons, Wyatt
Sent: Monday, February 13, 2006 9:39 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumproduct query(I think) or array entered
"with wildcard *
???"
So the answer is "NO" ?
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: Umlas, Robert [mailto:rumlasKPMG.COM]
Sent: Monday, February 13, 2006 6:34 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumproduct query(I think) or array entered
"with wildcard *
???"
=SUMPRODUCT((LEFT($A$2:$A$6,11)=A17)*$B$2:$B$6)
*** ANY TAX ADVICE IN THIS COMMUNICATION IS NOT INTENDED OR
WRITTEN BY
KPMG TO BE USED, AND CANNOT BE USED, BY A CLIENT OR ANY
OTHER PERSON OR
ENTITY FOR THE PURPOSE OF (i) AVOIDING PENALTIES THAT MAY BE
IMPOSED ON
ANY TAXPAYER OR (ii) PROMOTING, MARKETING OR RECOMMENDING TO
ANOTHER
PARTY ANY MATTERS ADDRESSED HEREIN. ***
Any advice in this communication is limited to the
conclusions
specifically set forth herein and is based on the
completeness and
accuracy of the stated facts, assumptions and/or
representations
included. In rendering our advice, we may consider tax
authorities that
are subject to change, retroactively and/or prospectively,
and any such
changes could affect the validity of our advice. We will not
update our
advice for subsequent changes or modifications to the law
and
regulations, or to the judicial and administrative
interpretations
thereof.
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Lemmons, Wyatt
Sent: Monday, February 13, 2006 9:15 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Sumproduct query(I think) or array entered
"with wildcard *
???"
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
>
>
------------------------------------------------------------
------------
> --
> 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
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.6/257 -
Release Date:
> 10/02/2006
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.6/257 -
Release Date:
> 10/02/2006
>
>
>
------------------------------------------------------------
------------
--
> 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
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.5/256 -
Release Date:
2/10/2006
>
>
------------------------------------------------------------
------------
--
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 information in this email is confidential and may be
legally
privileged. It is intended solely for the addressee. Access
to this
email by anyone else is unauthorized.
If you are not the intended recipient, any disclosure,
copying,
distribution or any action taken or omitted to be taken in
reliance on
it, is prohibited and may be unlawful. When addressed to our
clients any
opinions or advice contained in this email are subject to
the terms and
conditions expressed in the governing KPMG client engagement
letter.
************************************************************
************
*****
------------------------------------------------------------
------------
--
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 information in this email is confidential and may be
legally privileged. It is intended solely for the
addressee. Access to this email by anyone else is
unauthorized.
If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be
taken in reliance on it, is prohibited and may be unlawful.
When addressed to our clients any opinions or advice
contained in this email are subject to the terms and
conditions expressed in the governing KPMG client engagement
letter.
************************************************************
*****************
------------------------------------------------------------
--------------
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
|