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]

ok, I think I had a lightbulb moment in understanding - basically, the guys input the seat type in Col A (as I see you can have duplicates), every day, and this is their total seats being built. Then your formulas in Col E etc. are dictating from that list which seats have which parts. (sorry, it took a long time for me to understand which way around this was working). Its not really the best way to do multiplication with so many countifs, and I really wouldn't want to be you right now , facing the amount of formulas you are about to change because of that !

So, I think if you change all the relevant formulas to use SUMIFS instead of COUNTIF, adding in the B Column reference as I have - it should work.

=SUMIFS(B3:B25,C3:C25,"187618")

Of course it means they have to add a qty in every line (even if just 1). No qty added = zero multiplier .. and that gets you zero...

Good Luck!...

Rob

 

[klickoe]

There's probably a couple hundred seat types.

You're correct that the standards are a seat family. Each catalog # is a seat type. Each family is composed of seats that are very similar. They'll be built almost identically and look mostly the same when built. There will be minor differences though. We'll take that 17 no-iso for example. The no-iso is a reference to a style of chassis a seat can get. Each seat only gets one chassis. So what this means when it says it needs 17 no-iso under the standards section, is that out of all the cat#'s in col A, there are 17 seats that belong to the standard family that will receive no iso parts.

Does that clear it up?

 

[klickoe]

AWESOME!! Thank you!

Yeah changing gonna suck a little, but it's better than I was expecting.

 

[RobP]

thanks for feedback. If it were me, and I faced changing all those formulas, I think I would change my sheet to reflect something like this instead :

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2									CAT # SEAT Types
3	DATA ENTRY			TOTAL		3	2	0	0	0	0	1	0	0	0	0	1	0	0	0
4	Cat #	Qty		Qty Part #	Part #	187017VW712	187000HA710	187618HA711	187652HW711	187618VA712	187002VN712	187040VN712	187025HN711	187032HL711	187025VA712	187024HL711	187040VN712	187025VA712	187040HN711	187030VA712
5	187017VW712	3		6	782394 No Iso	1						3
6	187000HA710	2		2	116330 No Iso Track		1
7	187040VN712	1		10	783147 Iso	2	1					2
8				3	116331 Iso Track	1
9				5	783300 Bellow		1					3
10				14	315407 Compressor	4						2
11				9	315989 Shifter	2	1					1
12				1	783263 OPS							1
13				8	310373 HB Frame	1	2					1
14				0	310374 MB Frame
15				10	783322 Riser box	2	2
16				3	783349 Riser w/ Elec		1					1
17				3	112706 Track Kit	1
18				0	316082 Restriction Kit
19
Sheet2

Cell Formulas
Range	Formula
F3:T3	F3	=IFNA(VLOOKUP(F$4,$A5:$B25,2,FALSE),0)
D5:D18	D5	=SUMPRODUCT($F$3:$T$3,F5:T5)

This way, its a simple matter of adding new PArt # to the list as they come up, and also, adding any new seat type across the columns (so you can add a new seat type, and enter its BOM very quickly and simply). The results you are after would then come from column D with the totals.

A small bit of pain to start, but much more manageable as new items come up to be added... (numbers of prts for each seat BOM is made up, example of course on my part above..)

Hope it helps

Rob

 

[klickoe]

Thanks for the advice. I might just do that when I'm not chasing other things down. Got curious and ran a report to see how many different catalogs we currently have in our system..... there's nearly 500, and they keep putting more in.....