only include cells beginning with specified character or number
 

I want to sum the "qty" field in a database based on the "job number". I
only want job numbers beginning with 1,2,3 or 4. Below is a list of assorted
job number/alpha I might use.
Job number
103811
310225
350875-6
350875-7
350875-8
350875-8
370245-1
SKU#58214

Another formula I need is to sum "shipping lbs" in a database based on the
product name. I only want products beginning with "c". Below is a list of
assorted products I might use.
Product
Curriculum
Curriculum
Curriculum
VBS FLYER RETAIL
VBS FLYER WHOLESALE
VBS FLYER DENOMS
JUST ADD KIDS - RETAIL
PICK UP N DO SAMPLER
Curriculum
Curriculum
PRESCHOOLER'S BIBLE

Hi
use something like
=SUMPRODUCT(--(LEFT(A1:A100)="1"),B1:B100)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"Spunky" schrieb im Newsbeitrag
...
I want to sum the "qty" field in a database based on the "job
number". I
only want job numbers beginning with 1,2,3 or 4. Below is a list of
assorted
job number/alpha I might use.
Job number
103811
310225
350875-6
350875-7
350875-8
350875-8
370245-1
SKU#58214

Another formula I need is to sum "shipping lbs" in a database based
on the
product name. I only want products beginning with "c". Below is a
list of
assorted products I might use.
Product
Curriculum
Curriculum
Curriculum
VBS FLYER RETAIL
VBS FLYER WHOLESALE
VBS FLYER DENOMS
JUST ADD KIDS - RETAIL
PICK UP N DO SAMPLER
Curriculum
Curriculum
PRESCHOOLER'S BIBLE

try this idea
=SUMPRODUCT((LEFT(A4:A14)={"1","2","3","4"})*B4:B1 4)

--
Don Guillett
SalesAid Software

"Spunky" wrote in message
...
I want to sum the "qty" field in a database based on the "job number". I
only want job numbers beginning with 1,2,3 or 4. Below is a list of
assorted
job number/alpha I might use.
Job number
103811
310225
350875-6
350875-7
350875-8
350875-8
370245-1
SKU#58214

Another formula I need is to sum "shipping lbs" in a database based on the
product name. I only want products beginning with "c". Below is a list
of
assorted products I might use.
Product
Curriculum
Curriculum
Curriculum
VBS FLYER RETAIL
VBS FLYER WHOLESALE
VBS FLYER DENOMS
JUST ADD KIDS - RETAIL
PICK UP N DO SAMPLER
Curriculum
Curriculum
PRESCHOOLER'S BIBLE

1.

a) =SUM(SUMIF(A2:A9,{1,2,3,4}&"*",B2:B9))

b) =SUMPRODUCT(--ISNUMBER(MATCH(LEFT(A2:A9)+0,{1,2,3,4},0)),B2:B9)

{1,2,3,4} in (b) can be replaced with a range which houses those
digits.

2.

=SUMIF(A2:A9,C2&"*",B2:B9))

where C2 houses the letter C.


Spunky Wrote:
I want to sum the "qty" field in a database based on the "job number".
I
only want job numbers beginning with 1,2,3 or 4. Below is a list of
assorted
job number/alpha I might use.
Job number
103811
310225
350875-6
350875-7
350875-8
350875-8
370245-1
SKU#58214

Another formula I need is to sum "shipping lbs" in a database based on
the
product name. I only want products beginning with "c". Below is a
list of
assorted products I might use.
Product
Curriculum
Curriculum
Curriculum
VBS FLYER RETAIL
VBS FLYER WHOLESALE
VBS FLYER DENOMS
JUST ADD KIDS - RETAIL
PICK UP N DO SAMPLER
Curriculum
Curriculum
PRESCHOOLER'S BIBLE


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=319762