Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find all cells with a number and mulitply | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Removing ' character from cells | Excel Discussion (Misc queries) | |||
Count number of shaded cells | Excel Discussion (Misc queries) |