SUMPRODUCT Over Multiple Columns

[thewiseguy]

Hello all,

I am looking for help creating a single formula that will give me the SUM of multiple products that we offer. The sheet below is our Bill of Materials. This grabs the unique values from our input page and now I want to find the total quantity of products from our INPUT page, but they span over multiple columns. See the screenshot below the BOM page. (Please note, the current formula in column I will be removed, as the data is now coming from multiple hidden pages - I am trying to simplify).

v2024.38-1.xlsm
	B							I	J
3	Mfg/Item							Quantity	Unit Cost
4	HALCO 10.5W LED 4ft T8 Lamp [4K]								$3.75
5	HALCO 8in LED Downlight [12W-MCCT]							8	$39.00
6	HONYA LED 2ft 220W Linear Highbay [160W-MCCT]							3	$73.00
7	MAXLITE 2X2 LED Flat Panel [18W-MCCT]							361	$25.81
8	MAXLITE 2X2 LED Flat Panel [36W-MCCT]							3	$25.81
9	MAXLITE 2x2 LED Troffer Retrofit [25W-MCCT]							1	$50.00
10	MAXLITE 2X4 LED Flat Panel [27W-MCCT]							66	$36.56
11	MAXLITE 8ft LED Value Strip Fixture [55W-MCCT]							84	$53.76
12	MESTER 205W LED Linear Highbay [150W-MCCT-480V]							12	$131.64
13	LM Steel Bullhorn with 2 Arms at 180 Degrees							4	$1,500.00
14	LM Tenon Arm for Wall Mounting							2	$10.00
15	ENVISIONLED Daylight & Bi-Level Motion Sensor							28	$0.67
16	TBD 1/2-EMT Conduit 1/2-Inch x 10'							15	$500.00
17	TBD U Channel Beam Clamp for Kindorf							400	$1,000.00
18	ROSELLE Roll Off Dumpsters – 30 Yard							2	$2,000.00
19	EASTERNHIGHREACH 19 ft. Scissor Lift, Electric, Narrow [M]							1	$3.90
20	EASTERNHIGHREACH 24-26 ft. Scissor Lift, Electric, Wide [M]							1	$3.90
21	VEOLIA Supply 4' Lamp Box							14
22	VEOLIA Supply U-Tube Box							38
BOM

Cell Formulas
Range	Formula
B4:B22	B4	=LET(v,VSTACK('ECM (BOM)'!B4#,'Accessories (BOM)'!B4#,'Controls (BOM)'!B4#,'Materials (BOM)'!B4#,'Rental Equipment (BOM)'!B4:B26, 'Recycle (BOM)'!B43#),FILTER(v,v<>""))
I5:I22	I5	=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(B5,ECM_BOM_Table,7,0),VLOOKUP(B5,CONTROLS_BOM_TABLE,7,0)),VLOOKUP(B5,ACCESSORIES_BOM_TABLE,7,0)),VLOOKUP(B5,MATERIALS_BOM_TABLE,7,0)),VLOOKUP(B5,RENTAL_EQUIPMENT_BOM_TABLE,7,0)),VLOOKUP(B5,Recycle_BOM_Table,7,0)),"")
Dynamic array formulas.

This is our INPUT page.
The products from our BOM page can be found in columns: R, W, Y, AA, AC, AE, AG
The quantities from our BOM page can be found in columns: U, X, Z, AB, AD, AF, AH

Any help is appreciated as I hope I have been clear in what I am trying to accomplish!

v2024.38-1.xlsm
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
2	ECM							Accessory 1		Accessory 2		Control		Materials 1		Materials 2		Add. Labor
3	Product	ECM	ECM SubCategory	Override	Override Qty	ECM QTY	Labor Qty	Accessory Name	QTY	Accessory Name	QTY	Control Name	QTY	Material Name	QTY	Material Name	QTY	Add. Labor	QTY
4	Fixture	Flat Panel	MAXLITE 2X2 LED Flat Panel [18W-MCCT]			2	2	LM Steel Bullhorn with 2 Arms at 180 Degrees	2	LM Tenon Arm for Wall Mounting	2	ENVISIONLED Daylight & Bi-Level Motion Sensor	14	TBD U Channel Beam Clamp for Kindorf	200	TBD 1/2-EMT Conduit 1/2-Inch x 10'	15	Re-wire existing	2
Input

Cell Formulas
Range	Formula
U4	U4	=IFERROR(IFS(R4="EMA No Measure", 0, S4="Yes", T4, P4="Lamp", Helper!G4 * J4, TRUE, J4),"")
V4	V4	=IFS(R4="EMA No Measure", 0, S4="Yes", T4,S4="",J4)

 

[Vogateer]

I might be misunderstanding, but it sounds like you want to count the number of products you offer, but those products are listed across the rows in the Input sheet. Is that right? And you would like to do something with the QTY columns, too? I'm not sure how you want to SUM these, when it seems like you're after a COUNT for the products.

 

[thewiseguy]

I might be misunderstanding, but it sounds like you want to count the number of products you offer, but those products are listed across the rows in the Input sheet. Is that right? And you would like to do something with the QTY columns, too? I'm not sure how you want to SUM these, when it seems like you're after a COUNT for the products.

correct. I was able to figure it out using a VSTACK formula. Thank you.