Multiply one cell by the cell next to it based on the text in the first cell.

[klickoe]

Hello all,

Not sure what I'm trying to do is possible.
I made this sheet for the company I work for to count parts needed based on the catalog number for that days run. These runs can have any one of a few hundred different catalog numbers. This has worked well because each order set up for these catalogs would be a quantity of one, so a simple Countif function did the trick. Now though orders are being made with more than a quantity of one.

Is it possible to do a formula that if a cell within a range equals a specific text then multiply it by the cell to it's right?

 

[RobP]

Hi Welcome to the forum,

in short, most likely yes, something can be done - best to try post some example data up here so we can see what you are talking about, and then see how to help ?

Try to use the xl2bb function above to copy the relevant parts of an example sheet to us for quicker results back to you..
Rob

 

[klickoe]

Here is a portion of the sheet. The whole thing is too large to copy it all. Both columns C and F take certain parts of the Cat# entered into column A.

Seat parts.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Data Input			Parts Per Seat Family
3	Cat#	QTY		Standards			Premiums			Uppers/Single TB			Duals
4	187017VW712		187017	Part #	Qty Needed	7VW712	Part #	Qty Needed		Part #	Qty Needed		Part #	Qty Needed
5	187000HA710		187000	782394 No Iso	17	0HA710	782394 No Iso	4		315072 HB Frame	13		315181 BK Frame	9
6	187618HA711		187618	116330 No Iso Track	17	8HA711	116330 No Iso Track	4		315722 MB Frame	10		315140 CU Frame	9
7	187652HW711		187652	783147 Iso	13	2HW711	783147 Iso	1		315079 Door	19		315077 Extension	9
8	187618VA712		187618	116331 Iso Track	13	8VA712	116331 Iso Track	1		315378 Box	19		315076 Box	9
9	187002VN712		187002	783300 Bellow	13	2VN712	783300 Bellow	5		518770 Doc Holder	4		315079 Door	9
10	187040VN712		187040	315407 Compressor	0	0VN712	313253 BK Frame	5		783263 OPS	3		518646 Cover Panel	9
11	187025HN711		187025	315989 Shifter	1	5HN711	307580 CU Frame	5		187228PS Box/Rec	0		187221PS Box	0
12	187032HL711		187032	783263 OPS	0	2HL711	Kenworth	2		187227PS Box	0		187048% Upper	0
13	187025VA712		187025	310373 HB Frame	21	5VA712	Peterbuilt	3
14	187024HL711		187024	310374 MB Frame	9	4HL711	783320 Heater	3		Covers
15	187040VN712		187040	783322 Riser box	0	0VN712	783263 OPS	0		Standards	Highback	Blk Cloth 316041C710		3
16	187025VA712		187025	783349 Riser w/ Elec	0	5VA712						Grey Cloth 316041C711		9
17	187040HN711		187040	112706 Track Kit	0	0HN711						Blk Vinyl 316045C712		5
18	187030VA712		187030	316082 Restriction Kit	0	0VA712						Grey Vinyl 316045C713		4
19	187000HA711		187000			0HA711					Mid back	Blk Cloth 316408C710		1
20	187619HA710		187619	Total # of Seats		9HA710	Totals for Shared Parts					Grey Cloth 316408C711		1
21	187000HN711		187000	Standards	30	0HN711	783147 ISO Chassis	14				Blk Vinyl 316409C712		2
22	187618VA712		187618	Premiums	5	8VA712	116331 ISO Tracks	14				Grey Vinyl 316409C713		5
23	187001VA713		187001	Uppers	4	1VA713	782394 No-ISO Chassis	21			Cushions (Includes Uppers/TB)	Blk Cloth 316042C710		7
24	187001VN713		187001	Single TB	19	1VN713	116330 No-ISO Tracks	21				Grey Cloth 316042C711		18
25	187032VN713		187032	Dual TB	9	2VN713	783300 Bellows	18				Blk Vinyl 316046C712		12
26	187040VN713		187040	Dual Upper	0	0VN713	783263 OPS	3				Grey Vinyl 316046C713		16
Sheet1

Cell Formulas
Range	Formula
K5	K5	=COUNTIF($C$4:$C$1553,187032)+COUNTIF($C$4:$C$1553,187034)+COUNTIF($C$4:$C$1553,187024)+COUNTIF($C$4:$C$1553,187026)+COUNTIF($C$4:$C$1553,187028) +COUNTIF($C$4:$C$1553,187030)
K6	K6	=COUNTIF($C$4:$C$1553,187033)+COUNTIF($C$4:$C$1553,187035)+COUNTIF($C$4:$C$1553,187025)+COUNTIF($C$4:$C$1553,187027)+COUNTIF($C$4:$C$1553,187029) +COUNTIF($C$4:$C$1553,187031)
K7	K7	=COUNTIF($C$4:$C$1553,187024)+COUNTIF($C$4:$C$1553,187025)+COUNTIF($C$4:$C$1553,187026)+COUNTIF($C$4:$C$1553,187027)+COUNTIF($C$4:$C$1553,187028) +COUNTIF($C$4:$C$1553,187029) +COUNTIF($C$4:$C$1553,187030) +COUNTIF($C$4:$C$1553,187031)+K11+K12
K8,E8,E6,H11,H8,H6,N8,N6	K8	=K7
K9	K9	=COUNTIF($C$4:$C$1553,187028)+COUNTIF($C$4:$C$1553,187029)+COUNTIF($C$4:$C$1553,187030)+COUNTIF($C$4:$C$1553,187031)+K11
K10	K10	=COUNTIF($C$4:$C$1553,187026)+COUNTIF($C$4:$C$1553,187027)+COUNTIF($C$4:$C$1553,187030)+COUNTIF($C$4:$C$1553,187031) +COUNTIF($C$4:$C$1553,187034) +COUNTIF($C$4:$C$1553,187035)
K11	K11	=COUNTIF($C$4:$C$1553,187228)
K12	K12	=COUNTIF($C$4:$C$1553,187227)
N5	N5	=N7+N12
N7	N7	=COUNTIF($C$4:$C$1554,187040)+N11
N9	N9	=N7
N10	N10	=N7
N11	N11	=COUNTIF($C$4:$C$1553,187221)
N12	N12	=E26
H5	H5	=COUNTIF($C$4:$C$1553,187016)+COUNTIF($C$4:$C$1553,187017)+COUNTIF($C$4:$C$1553,187018)+COUNTIF($C$4:$C$1553,187019)+COUNTIF($C$4:$C$1553,187020) +COUNTIF($C$4:$C$1553,187021) +COUNTIF($C$4:$C$1553,187022) +COUNTIF($C$4:$C$1553,187023)
H7	H7	=COUNTIF($C$4:$C$1553,187648)+COUNTIF($C$4:$C$1553,187649)+COUNTIF($C$4:$C$1553,187650)+COUNTIF($C$4:$C$1553,187651)+COUNTIF($C$4:$C$1553,187652) +COUNTIF($C$4:$C$1553,187653) +COUNTIF($C$4:$C$1553,187654) +COUNTIF($C$4:$C$1553,187655)
H9	H9	=H5+H7
H10	H10	=H12+H13
H12	H12	=COUNTIF($C$4:$C$1553,187648)+COUNTIF($C$4:$C$1553,187649)+COUNTIF($C$4:$C$1553,187650)+COUNTIF($C$4:$C$1553,187651)+COUNTIF($C$4:$C$1553,187016) +COUNTIF($C$4:$C$1553,187017) +COUNTIF($C$4:$C$1553,187018) +COUNTIF($C$4:$C$1553,187019)
H13	H13	=COUNTIF($C$4:$C$1553,187652)+COUNTIF($C$4:$C$1553,187653)+COUNTIF($C$4:$C$1553,187654)+COUNTIF($C$4:$C$1553,187655)+COUNTIF($C$4:$C$1553,187020) +COUNTIF($C$4:$C$1553,187021) +COUNTIF($C$4:$C$1553,187022) +COUNTIF($C$4:$C$1553,187023)
H14	H14	=COUNTIF($C$4:$C$1553,187017)+COUNTIF($C$4:$C$1553,187019)+COUNTIF($C$4:$C$1553,187021)+COUNTIF($C$4:$C$1553,187023)+COUNTIF($C$4:$C$1553,187649) +COUNTIF($C$4:$C$1553,187651) +COUNTIF($C$4:$C$1553,187653) +COUNTIF($C$4:$C$1553,187655)
H15	H15	=COUNTIF($C$4:$C$1553,187018)+COUNTIF($C$4:$C$1553,187019)+COUNTIF($C$4:$C$1553,187022)+COUNTIF($C$4:$C$1553,187023)+COUNTIF($C$4:$C$1553,187650) +COUNTIF($C$4:$C$1553,187651) +COUNTIF($C$4:$C$1553,187654) +COUNTIF($C$4:$C$1553,187655)
E5	E5	=COUNTIF($C$4:$C$1555,187000)+COUNTIF($C$4:$C$1555,187001)+COUNTIF($C$4:$C$1555,187002)+COUNTIF($C$4:$C$1555,187003)+COUNTIF($C$4:$C$1555,187004)+COUNTIF($C$4:$C$1555,187005)+COUNTIF($C$4:$C$1555,187006)+COUNTIF($C$4:$C$1555,187007)+COUNTIF($C$4:$C$1555,187008)+COUNTIF($C$4:$C$1555,187009)+COUNTIF($C$4:$C$1555,187010)+COUNTIF($C$4:$C$1555,187011)+COUNTIF($C$4:$C$1555,187012)+COUNTIF($C$4:$C$1555,187013)+COUNTIF($C$4:$C$1555,187014)+COUNTIF($C$4:$C$1555,187015)+COUNTIF($C$4:$C$1555,187632)+COUNTIF($C$4:$C$1555,187633)+COUNTIF($C$4:$C$1555,187634)+COUNTIF($C$4:$C$1555,187635)+COUNTIF($C$4:$C$1555,187636)+COUNTIF($C$4:$C$1555,187637)+COUNTIF($C$4:$C$1555,187638)+COUNTIF($C$4:$C$1555,187638)+COUNTIF($C$4:$C$1555,187639)+COUNTIF($C$4:$C$1555,187640)+COUNTIF($C$4:$C$1555,187641)+COUNTIF($C$4:$C$1555,187642)+COUNTIF($C$4:$C$1555,187643)+COUNTIF($C$4:$C$1555,187644)+COUNTIF($C$4:$C$1555,187645)+COUNTIF($C$4:$C$1555,187646)+COUNTIF($C$4:$C$1555,187647)
E7	E7	=COUNTIF($C$4:$C$1553,187600)+COUNTIF($C$4:$C$1555,187601)+COUNTIF($C$4:$C$1555,187602)+COUNTIF($C$4:$C$1555,187603)+COUNTIF($C$4:$C$1555,187604)+COUNTIF($C$4:$C$1555,187605)+COUNTIF($C$4:$C$1555,187606)+COUNTIF($C$4:$C$1555,187607)+COUNTIF($C$4:$C$1555,187608)+COUNTIF($C$4:$C$1555,187609)+COUNTIF($C$4:$C$1555,187610)+COUNTIF($C$4:$C$1555,187611)+COUNTIF($C$4:$C$1555,187612)+COUNTIF($C$4:$C$1555,187613)+COUNTIF($C$4:$C$1555,187614)+COUNTIF($C$4:$C$1555,187615)+COUNTIF($C$4:$C$1555,187616)+COUNTIF($C$4:$C$1555,187617)+COUNTIF($C$4:$C$1555,187618)+COUNTIF($C$4:$C$1555,187619)+COUNTIF($C$4:$C$1555,187620)+COUNTIF($C$4:$C$1555,187621)+COUNTIF($C$4:$C$1555,187622)+COUNTIF($C$4:$C$1555,187623)+COUNTIF($C$4:$C$1555,187624)+COUNTIF($C$4:$C$1555,187625)+COUNTIF($C$4:$C$1555,187626)+COUNTIF($C$4:$C$1555,187627)+COUNTIF($C$4:$C$1555,187628)+COUNTIF($C$4:$C$1555,187629)+COUNTIF($C$4:$C$1555,187630)+COUNTIF($C$4:$C$1555,187631)
E9	E9	=COUNTIF($C$4:$C$1553,187002)+COUNTIF($C$4:$C$1553,187003)+COUNTIF($C$4:$C$1553,187006)+COUNTIF($C$4:$C$1553,187007)+COUNTIF($C$4:$C$1553,187010)+COUNTIF($C$4:$C$1553,187011)+COUNTIF($C$4:$C$1553,187014)+COUNTIF($C$4:$C$1553,187015)+COUNTIF($C$4:$C$1553,187602)+COUNTIF($C$4:$C$1553,187603)+COUNTIF($C$4:$C$1553,187606)+COUNTIF($C$4:$C$1553,187607)+COUNTIF($C$4:$C$1553,187610)+COUNTIF($C$4:$C$1553,187611)+COUNTIF($C$4:$C$1553,187614)+COUNTIF($C$4:$C$1553,187615) +COUNTIF($C$4:$C$1553,187618) +COUNTIF($C$4:$C$1553,187619) +COUNTIF($C$4:$C$1553,187622) +COUNTIF($C$4:$C$1553,187623) +COUNTIF($C$4:$C$1553,187626) +COUNTIF($C$4:$C$1553,187627) +COUNTIF($C$4:$C$1553,187630) +COUNTIF($C$4:$C$1553,187631) +COUNTIF($C$4:$C$1553,187634) +COUNTIF($C$4:$C$1553,187635) +COUNTIF($C$4:$C$1553,187638) +COUNTIF($C$4:$C$1553,187639) +COUNTIF($C$4:$C$1553,187642) +COUNTIF($C$4:$C$1553,187643) +COUNTIF($C$4:$C$1553,187646) +COUNTIF($C$4:$C$1553,187647)+COUNTIF($C$4:$C$1553,187682)+COUNTIF($C$4:$C$1553,187676)+COUNTIF($C$4:$C$1553,187678)+COUNTIF($C$4:$C$1553,187680)+COUNTIF($C$4:$C$1553,187681)+COUNTIF($C$4:$C$1553,187683)
E10	E10	=COUNTIF($C$4:$C$1553,187004)+COUNTIF($C$4:$C$1553,187005)+COUNTIF($C$4:$C$1553,187006)+COUNTIF($C$4:$C$1553,187007)+COUNTIF($C$4:$C$1553,187012)+COUNTIF($C$4:$C$1553,187013)+COUNTIF($C$4:$C$1553,187014)+COUNTIF($C$4:$C$1553,187015)+COUNTIF($C$4:$C$1553,187604)+COUNTIF($C$4:$C$1553,187605)+COUNTIF($C$4:$C$1553,187606)+COUNTIF($C$4:$C$1553,187607)+COUNTIF($C$4:$C$1553,187612)+COUNTIF($C$4:$C$1553,187613)+COUNTIF($C$4:$C$1553,187614)+COUNTIF($C$4:$C$1553,187615) +COUNTIF($C$4:$C$1553,187620) +COUNTIF($C$4:$C$1553,187621) +COUNTIF($C$4:$C$1553,187622) +COUNTIF($C$4:$C$1553,187623) +COUNTIF($C$4:$C$1553,187628) +COUNTIF($C$4:$C$1553,187629) +COUNTIF($C$4:$C$1553,187630) +COUNTIF($C$4:$C$1553,187631) +COUNTIF($C$4:$C$1553,187636) +COUNTIF($C$4:$C$1553,187637) +COUNTIF($C$4:$C$1553,187638) +COUNTIF($C$4:$C$1553,187639) +COUNTIF($C$4:$C$1553,187644) +COUNTIF($C$4:$C$1553,187645) +COUNTIF($C$4:$C$1553,187646) +COUNTIF($C$4:$C$1553,187647)
E11	E11	=COUNTIF($C$4:$C$1553,187600)+COUNTIF($C$4:$C$1553,187601)+COUNTIF($C$4:$C$1553,187602)+COUNTIF($C$4:$C$1553,187603)+COUNTIF($C$4:$C$1553,187604)+COUNTIF($C$4:$C$1553,187605)+COUNTIF($C$4:$C$1553,187606)+COUNTIF($C$4:$C$1553,187607)+COUNTIF($C$4:$C$1553,187608)+COUNTIF($C$4:$C$1553,187609)+COUNTIF($C$4:$C$1553,187610)+COUNTIF($C$4:$C$1553,187611)+COUNTIF($C$4:$C$1553,187612)+COUNTIF($C$4:$C$1553,187613)+COUNTIF($C$4:$C$1553,187614)+COUNTIF($C$4:$C$1553,187615) +COUNTIF($C$4:$C$1553,187632) +COUNTIF($C$4:$C$1553,187633) +COUNTIF($C$4:$C$1553,187634) +COUNTIF($C$4:$C$1553,187635) +COUNTIF($C$4:$C$1553,187636) +COUNTIF($C$4:$C$1553,187637) +COUNTIF($C$4:$C$1553,187638) +COUNTIF($C$4:$C$1553,187639) +COUNTIF($C$4:$C$1553,187640) +COUNTIF($C$4:$C$1553,187641) +COUNTIF($C$4:$C$1553,187642) +COUNTIF($C$4:$C$1553,187643) +COUNTIF($C$4:$C$1553,187644) +COUNTIF($C$4:$C$1553,187645) +COUNTIF($C$4:$C$1553,187646) +COUNTIF($C$4:$C$1553,187647)
E12	E12	=COUNTIF($C$4:$C$1553,187008)+COUNTIF($C$4:$C$1553,187009)+COUNTIF($C$4:$C$1553,187010)+COUNTIF($C$4:$C$1553,187011)+COUNTIF($C$4:$C$1553,187012)+COUNTIF($C$4:$C$1553,187013)+COUNTIF($C$4:$C$1553,187014)+COUNTIF($C$4:$C$1553,187015)+COUNTIF($C$4:$C$1553,187608)+COUNTIF($C$4:$C$1553,187609)+COUNTIF($C$4:$C$1553,187610)+COUNTIF($C$4:$C$1553,187611)+COUNTIF($C$4:$C$1553,187612)+COUNTIF($C$4:$C$1553,187613)+COUNTIF($C$4:$C$1553,187614)+COUNTIF($C$4:$C$1553,187615) +COUNTIF($C$4:$C$1553,187624) +COUNTIF($C$4:$C$1553,187625) +COUNTIF($C$4:$C$1553,187626) +COUNTIF($C$4:$C$1553,187627) +COUNTIF($C$4:$C$1553,187628) +COUNTIF($C$4:$C$1553,187629) +COUNTIF($C$4:$C$1553,187630) +COUNTIF($C$4:$C$1553,187631) +COUNTIF($C$4:$C$1553,187640) +COUNTIF($C$4:$C$1553,187641) +COUNTIF($C$4:$C$1553,187642) +COUNTIF($C$4:$C$1553,187643) +COUNTIF($C$4:$C$1553,187644) +COUNTIF($C$4:$C$1553,187645) +COUNTIF($C$4:$C$1553,187646) +COUNTIF($C$4:$C$1553,187647)+COUNTIF($C$4:$C$1553,187682)+COUNTIF($C$4:$C$1553,187678)+COUNTIF($C$4:$C$1553,187683)
E13	E13	=COUNTIF($C$4:$C$1553,187000)+COUNTIF($C$4:$C$1553,187002)+COUNTIF($C$4:$C$1553,187004)+COUNTIF($C$4:$C$1553,187006)+COUNTIF($C$4:$C$1553,187008)+COUNTIF($C$4:$C$1553,187010)+COUNTIF($C$4:$C$1553,187012)+COUNTIF($C$4:$C$1553,187014)+COUNTIF($C$4:$C$1553,187600)+COUNTIF($C$4:$C$1553,187602)+COUNTIF($C$4:$C$1553,187604)+COUNTIF($C$4:$C$1553,187606)+COUNTIF($C$4:$C$1553,187608)+COUNTIF($C$4:$C$1553,187610)+COUNTIF($C$4:$C$1553,187612)+COUNTIF($C$4:$C$1553,187614) +COUNTIF($C$4:$C$1553,187616) +COUNTIF($C$4:$C$1553,187618) +COUNTIF($C$4:$C$1553,187620) +COUNTIF($C$4:$C$1553,187622) +COUNTIF($C$4:$C$1553,187624) +COUNTIF($C$4:$C$1553,187626) +COUNTIF($C$4:$C$1553,187628) +COUNTIF($C$4:$C$1553,187630) +COUNTIF($C$4:$C$1553,187632) +COUNTIF($C$4:$C$1553,187634) +COUNTIF($C$4:$C$1553,187636) +COUNTIF($C$4:$C$1553,187638) +COUNTIF($C$4:$C$1553,187640) +COUNTIF($C$4:$C$1553,187642) +COUNTIF($C$4:$C$1553,187644) +COUNTIF($C$4:$C$1553,187646)+COUNTIF($C$4:$C$1553,187042)+COUNTIF($C$4:$C$1553,187043)+COUNTIF($C$4:$C$1553,187201)+COUNTIF($C$4:$C$1553,187202)
E14	E14	=COUNTIF($C$4:$C$1553,187001)+COUNTIF($C$4:$C$1553,187003)+COUNTIF($C$4:$C$1553,187005)+COUNTIF($C$4:$C$1553,187007)+COUNTIF($C$4:$C$1553,187009)+COUNTIF($C$4:$C$1553,187011)+COUNTIF($C$4:$C$1553,187013)+COUNTIF($C$4:$C$1553,187015)+COUNTIF($C$4:$C$1553,187601)+COUNTIF($C$4:$C$1553,187603)+COUNTIF($C$4:$C$1553,187605)+COUNTIF($C$4:$C$1553,187607)+COUNTIF($C$4:$C$1553,187609)+COUNTIF($C$4:$C$1553,187611)+COUNTIF($C$4:$C$1553,187613)+COUNTIF($C$4:$C$1553,187615) +COUNTIF($C$4:$C$1553,187617) +COUNTIF($C$4:$C$1553,187619) +COUNTIF($C$4:$C$1553,187621) +COUNTIF($C$4:$C$1553,187623) +COUNTIF($C$4:$C$1553,187625) +COUNTIF($C$4:$C$1553,187627) +COUNTIF($C$4:$C$1553,187629) +COUNTIF($C$4:$C$1553,187631) +COUNTIF($C$4:$C$1553,187633) +COUNTIF($C$4:$C$1553,187635) +COUNTIF($C$4:$C$1553,187637) +COUNTIF($C$4:$C$1553,187639) +COUNTIF($C$4:$C$1553,187641) +COUNTIF($C$4:$C$1553,187643) +COUNTIF($C$4:$C$1553,187645) +COUNTIF($C$4:$C$1553,187647)+COUNTIF($C$4:$C$1553,187044)+COUNTIF($C$4:$C$1553,187045)
E15	E15	=COUNTIF($C$4:$C$1553,187042)+COUNTIF($C$4:$C$1553,187043)+COUNTIF($C$4:$C$1553,187044)+COUNTIF($C$4:$C$1553,187045)+COUNTIF($C$4:$C$1553,187201)
E16	E16	=(COUNTIF($C$4:$C$1553,'N:\Eklicko\Misc\[NMD test book.xlsx.xlsm]Catalog list'!A73))
E17	E17	=E15+E16
E18	E18	=COUNTIF($C$4:$C$1553,187260)+COUNTIF($C$4:$C$1553,187664)+COUNTIF($C$4:$C$1553,187680)+COUNTIF($C$4:$C$1553,187681)+COUNTIF($C$4:$C$1553,187682)+COUNTIF($C$4:$C$1553,187683)+COUNTIF($C$4:$C$1553,187676)+COUNTIF($C$4:$C$1553,187678)
F4:F26	F4	=RIGHT(A4,6)
E21	E21	=E13+E14
E22	E22	=H10
E23	E23	=COUNTIF($C$4:$C$1553,187032)+COUNTIF($C$4:$C$1553,187033)+COUNTIF($C$4:$C$1553,187034)+COUNTIF($C$4:$C$1553,187035)
E24	E24	=K7
E25	E25	=N7
E26	E26	=COUNTIF($C$4:$C$1553,187048)
C4:C26	C4	=LEFT(A4,6)
H21:H22	H21	=E7+H7
H23:H24	H23	=E5+H5
H25	H25	=E9+H9
H26	H26	=E12+H15+K10
N15	N15	=(COUNTIF($A$4:$A$1553,"187000HA710")+COUNTIF($A$4:$A$1553,"187000HA710-C")+COUNTIF($A$4:$A$1553,"187000HL710")+COUNTIF($A$4:$A$1553,"187000HN710")+COUNTIF($A$4:$A$1553,"187000HR710")+COUNTIF($A$4:$A$1553,"187002HA710")+COUNTIF($A$4:$A$1553,"187002HL710")+COUNTIF($A$4:$A$1553,"187002HN710")+COUNTIF($A$4:$A$1553,"187002HN710-C")+COUNTIF($A$4:$A$1553,"187002HR710")+COUNTIF($A$4:$A$1553,"187004HA710")+COUNTIF($A$4:$A$1553,"187004HA710-C")+COUNTIF($A$4:$A$1553,"187012HA710")+COUNTIF($A$4:$A$1553,"187042HA710")+COUNTIF($A$4:$A$1553,"187201HA710")+COUNTIF($A$4:$A$1553,"187202HA710")+COUNTIF($A$4:$A$1553,"187602HA710")+COUNTIF($A$4:$A$1553,"187602HA710-C")+COUNTIF($A$4:$A$1553,"187616HA710")+COUNTIF($A$4:$A$1553,"187616HN710")+COUNTIF($A$4:$A$1553,"187616HR710")+COUNTIF($A$4:$A$1553,"187618HA710")+COUNTIF($A$4:$A$1553,"187618HA710-C")+COUNTIF($A$4:$A$1553,"187620HR710")+COUNTIF($A$4:$A$1553,"187622HA710")+COUNTIF($A$4:$A$1553,"187626HN710")+COUNTIF($A$4:$A$1553,"187632HA710"))
N16	N16	=(COUNTIF($A$4:$A$1553,"187000HA711")+COUNTIF($A$4:$A$1553,"187000HA711-C")+COUNTIF($A$4:$A$1553,"187000HL711")+COUNTIF($A$4:$A$1553,"187000HN711")+COUNTIF($A$4:$A$1553,"187000HR711")+COUNTIF($A$4:$A$1553,"187002HA711")+COUNTIF($A$4:$A$1553,"187002HL711")+COUNTIF($A$4:$A$1553,"187002HN711")+COUNTIF($A$4:$A$1553,"187002HN711-C")+COUNTIF($A$4:$A$1553,"187002HR711")+COUNTIF($A$4:$A$1553,"187004HA711")+COUNTIF($A$4:$A$1553,"187004HA711-C")+COUNTIF($A$4:$A$1553,"187012HA711")+COUNTIF($A$4:$A$1553,"187042HA711")+COUNTIF($A$4:$A$1553,"187201HA711")+COUNTIF($A$4:$A$1553,"187202HA711")+COUNTIF($A$4:$A$1553,"187602HA711")+COUNTIF($A$4:$A$1553,"187602HA711-C")+COUNTIF($A$4:$A$1553,"187616HA711")+COUNTIF($A$4:$A$1553,"187616HN711")+COUNTIF($A$4:$A$1553,"187616HR711")+COUNTIF($A$4:$A$1553,"187618HA711")+COUNTIF($A$4:$A$1553,"187618HA711-C")+COUNTIF($A$4:$A$1553,"187622HA711")+COUNTIF($A$4:$A$1553,"187626HN711")+COUNTIF($A$4:$A$1553,"187632HA711"))
N17	N17	=COUNTIF($A$4:$A$1553,"187000VA712")+COUNTIF($A$4:$A$1553,"187000VA712-C")+COUNTIF($A$4:$A$1553,"187000VL712")+COUNTIF($A$4:$A$1553,"187000VN712")+COUNTIF($A$4:$A$1553,"187000VR712")+COUNTIF($A$4:$A$1553,"187002VA712")+COUNTIF($A$4:$A$1553,"187002VL712")+COUNTIF($A$4:$A$1553,"187002VN712")+COUNTIF($A$4:$A$1553,"187002VN712-C")+COUNTIF($A$4:$A$1553,"187002VR712")+COUNTIF($A$4:$A$1553,"187042VA712")+COUNTIF($A$4:$A$1553,"187042VN712")+COUNTIF($A$4:$A$1553,"187043VA712")+COUNTIF($A$4:$A$1553,"187201VA712")+COUNTIF($A$4:$A$1553,"187202VA712")+COUNTIF($A$4:$A$1553,"187600VA712")+COUNTIF($A$4:$A$1553,"187602VN712")+COUNTIF($A$4:$A$1553,"187602VN712-C")+COUNTIF($A$4:$A$1553,"187606VA712")+COUNTIF($A$4:$A$1553,"187614VA712")+COUNTIF($A$4:$A$1553,"187616VA712")+COUNTIF($A$4:$A$1553,"187616VL712")+COUNTIF($A$4:$A$1553,"187616VL712-C")+COUNTIF($A$4:$A$1553,"187616VN712")+COUNTIF($A$4:$A$1553,"187616VR712")+COUNTIF($A$4:$A$1553,"187618VA712")+COUNTIF($A$4:$A$1553,"187618VA712-C")+COUNTIF($A$4:$A$1553,"187618VR712")+COUNTIF($A$4:$A$1553,"187622VA712")+COUNTIF($A$4:$A$1553,"187624VN712")+COUNTIF($A$4:$A$1553,"187626VN712")+COUNTIF($A$4:$A$1553,"187626VN712-C")+COUNTIF($A$4:$A$1553,"187630VA712")+COUNTIF($A$4:$A$1553,"187632VR712")+COUNTIF($A$4:$A$1553,"187638VN712")+COUNTIF($A$4:$A$1553,"187664VN712")+COUNTIF($A$4:$A$1553,"187676VA712")+COUNTIF($A$4:$A$1553,"187678VL712")+COUNTIF($A$4:$A$1553,"187680VA712")+COUNTIF($A$4:$A$1553,"187680VN712")+COUNTIF($A$4:$A$1553,"187681VA712")+COUNTIF($A$4:$A$1553,"187682VN712")+COUNTIF($A$4:$A$1553,"187683VA712")
N18	N18	=COUNTIF($A$4:$A$1553,"187000VA713")+COUNTIF($A$4:$A$1553,"187000VA713-C")+COUNTIF($A$4:$A$1553,"187000VL713")+COUNTIF($A$4:$A$1553,"187000VN713")+COUNTIF($A$4:$A$1553,"187000VR713")+COUNTIF($A$4:$A$1553,"187002VA713")+COUNTIF($A$4:$A$1553,"187002VL713")+COUNTIF($A$4:$A$1553,"187002VN713")+COUNTIF($A$4:$A$1553,"187002VN713-C")+COUNTIF($A$4:$A$1553,"187002VR713")+COUNTIF($A$4:$A$1553,"187004VN713")+COUNTIF($A$4:$A$1553,"187004VN713-C")+COUNTIF($A$4:$A$1553,"187042VA713")+COUNTIF($A$4:$A$1553,"187042VN713")+COUNTIF($A$4:$A$1553,"187043VA713")+COUNTIF($A$4:$A$1553,"187201VA713")+COUNTIF($A$4:$A$1553,"187202VA713")+COUNTIF($A$4:$A$1553,"187232VN713")+COUNTIF($A$4:$A$1553,"187600VA713")+COUNTIF($A$4:$A$1553,"187602VN713")+COUNTIF($A$4:$A$1553,"187602VN713-C")+COUNTIF($A$4:$A$1553,"187606VA713")+COUNTIF($A$4:$A$1553,"187614VA713")+COUNTIF($A$4:$A$1553,"187616VA713")+COUNTIF($A$4:$A$1553,"187616VL713")+COUNTIF($A$4:$A$1553,"187616VL713-C")+COUNTIF($A$4:$A$1553,"187616VN713")+COUNTIF($A$4:$A$1553,"187616VR713")+COUNTIF($A$4:$A$1553,"187618VA713")+COUNTIF($A$4:$A$1553,"187618VA713-C")+COUNTIF($A$4:$A$1553,"187618VR713")+COUNTIF($A$4:$A$1553,"187622VA713")+COUNTIF($A$4:$A$1553,"187624VN713")+COUNTIF($A$4:$A$1553,"187626VN713")+COUNTIF($A$4:$A$1553,"187626VN713-C")+COUNTIF($A$4:$A$1553,"187630VA713")+COUNTIF($A$4:$A$1553,"187632VR713")+COUNTIF($A$4:$A$1553,"187638VN713")+COUNTIF($A$4:$A$1553,"187664VN713")+COUNTIF($A$4:$A$1553,"187680VA713")+COUNTIF($A$4:$A$1553,"187680VN713")+COUNTIF($A$4:$A$1553,"187681VA713")+COUNTIF($A$4:$A$1553,"187682VN713")+COUNTIF($A$4:$A$1553,"187683VA713")
N19	N19	=COUNTIF($A$4:$A$1553,"187001HA710")+COUNTIF($A$4:$A$1553,"187001HL710")+COUNTIF($A$4:$A$1553,"187001HN710")+COUNTIF($A$4:$A$1553,"187001HA710-C")+COUNTIF($A$4:$A$1553,"187001HR710")+COUNTIF($A$4:$A$1553,"187003HA710")+COUNTIF($A$4:$A$1553,"187003HR710")+COUNTIF($A$4:$A$1553,"187009HN710")+COUNTIF($A$4:$A$1553,"187045HA710")+COUNTIF($A$4:$A$1553,"187233HN710")+COUNTIF($A$4:$A$1553,"187617HA710")+COUNTIF($A$4:$A$1553,"187619HA710")+COUNTIF($A$4:$A$1553,"187619HA710-C")+COUNTIF($A$4:$A$1553,"187627HN710")
N20	N20	=COUNTIF($A$4:$A$1553,"187001HA711")+COUNTIF($A$4:$A$1553,"187001HL711")+COUNTIF($A$4:$A$1553,"187001HN711")+COUNTIF($A$4:$A$1553,"187001HN711-C")+COUNTIF($A$4:$A$1553,"187003HA711")+COUNTIF($A$4:$A$1553,"187003HR711")+COUNTIF($A$4:$A$1553,"187009HN711")+COUNTIF($A$4:$A$1553,"187045HA711")+COUNTIF($A$4:$A$1553,"187233HN711")+COUNTIF($A$4:$A$1553,"187619HA711")+COUNTIF($A$4:$A$1553,"187619HA711-C")+COUNTIF($A$4:$A$1553,"187627")
N21	N21	=COUNTIF($A$4:$A$1553,"187001VA712")+COUNTIF($A$4:$A$1553,"187001VN712")+COUNTIF($A$4:$A$1553,"187001VN712-C")+COUNTIF($A$4:$A$1553,"187001VR712")+COUNTIF($A$4:$A$1553,"187003VR712")+COUNTIF($A$4:$A$1553,"187044VA712")+COUNTIF($A$4:$A$1553,"187044VN712")+COUNTIF($A$4:$A$1553,"187233VN712")+COUNTIF($A$4:$A$1553,"187617VA712")+COUNTIF($A$4:$A$1553,"187617VR712")+COUNTIF($A$4:$A$1553,"187619VA712")+COUNTIF($A$4:$A$1553,"187619VA712-C")+COUNTIF($A$4:$A$1553,"187619VN712")
N22	N22	=COUNTIF($A$4:$A$1553,"187001VA713")+COUNTIF($A$4:$A$1553,"187001VN713")+COUNTIF($A$4:$A$1553,"187001VN713-C")+COUNTIF($A$4:$A$1553,"187001VR713")+COUNTIF($A$4:$A$1553,"187003VR713")+COUNTIF($A$4:$A$1553,"187044VA713")+COUNTIF($A$4:$A$1553,"187044VN713")+COUNTIF($A$4:$A$1553,"187233VN713")+COUNTIF($A$4:$A$1553,"187617VA713")+COUNTIF($A$4:$A$1553,"187617VR713")+COUNTIF($A$4:$A$1553,"187619VA713")+COUNTIF($A$4:$A$1553,"187619VA713-C")+COUNTIF($A$4:$A$1553,"187619VN713")+COUNTIF($A$4:$A$1553,"187627VN713")
N23:N26	N23	=N15+N19+N39+N43

 

[RobP]

great, thanks - what we need now is a little elaboration on your problem :

"Is it possible to do a formula that if a cell within a range equals a specific text then multiply it by the cell to it's right?"

in relation to the sheet you show. ? eg. what text, which columns etc. ?

thanks
Rob

 

[klickoe]

Columns A & B will be the only ones that data will be manually entered. Basically what the sheet currently does is treats everything in Column A as a quantity of one (originally there was no QTY column). So if one of these numbers in column A has a quantity of 6, this would leave the counts in the "QTY Needed" columns 5 short. I'm trying to figure out how to keep the count accurate if the quantity is more than one.

Not sure if it's a good thought, but my first idea was something along the lines of a if function. For example if a cell equaled 187000HA710 than it would than look at the cell to it's right and use that number. But since column A can be anything, and there could be repeat numbers with different quantities I'm not sure where to begin.

 

[RobP]

what if you were to put brackets around all your countIf statements, and just multiply them by the qty in Col B ?

 

[RobP]

sorry, looks like you cannot multiply with the way the formulas are. I'm struggling to understand how you work the sheet.

So you have a list of part numbers in Col A that someone decides how many of they want (Col B). Can you explain a little as to what the SEATS are referring to, as the formulas in there seem to refer back to Col A (well, Col C, but thats just a subset of A). apologies for my confusion.....

 

[klickoe]

I apologize for a confusing sheet, haha. I'm pretty new using excel for anything more complicated than basic addition / multiplication type formulas, so there was probably an easier way to do what I have so far.

Basically col A is a catalog number that is referring to a type of seat. These particular ones would be like something you'd find in a semi, or mid size truck like a U-Haul. Each number in col A is a type of seat. Col C looks at the first 6 digits of the number because these dictate what features the seat will have. While col F looks at the last 6 digits. These dictate the color and material the covers are made of, as well as if the seat will have armrests or not.

Does that help?

 

[klickoe]

I created this sheet for the Team lead of the line that builds these seats. Due to multiple issues he was getting an excel sheet with the build schedule for the day. So he was having to go through each "Cat#" and try to add up all the different parts he needed. I made this sheet so all he would have to do is copy the cat#'s from build schedule and paste them into this sheet and it counted everything for him.

 

[RobP]

Apologies again - still lost.

So you are telling me you have in Col. A over 1500 types of Seat.

If I look at your "Standards" (which I assume is a seat family), "why" does it need 17 * 782394 No ISO Parts ? I mean if I look at your formula, it just counts a number of different seat types in Col A to come up with that 17 ? I can't grasp the relationship between your Cat no (seat types), Standards (are they seat types ?), and Parts ..

cheers
Rob