SUMIFS or SUMPRODUCT?

T

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
954
Office Version
  1. 365
Platform
  1. Windows
Hi all. The below worksheet is a summary of items for a specific project. Column A are the unique values of all of the products. I am trying to find the total number of each product in the project (column F) but the products are setup in multiple columns on another sheet.

v2023 (version 1).xlsb
ABCDEF
2Mfg/ItemItemApplicationTypeManufacturerPart #Fix. Qty
3EIKO 1L 24W LED 4ft T5 Lamp 5K1L 24W LED 4ft T5 Lamp 5KLinear_LampEIKOLED24WT5HO/46/850-G9D
4ENVISIONLED 8W LED Round Downlight [MCCT]8W LED Round Downlight [MCCT]DownlightENVISIONLEDLED-DLJBX-ADJ-3-8W-5CCT-BLK-R
5HALCO Joiner Bracket for LWA WrapJoiner Bracket for LWA WrapAccessoryHALCOLWA-D/JB
6MAXLITE 3L 25W LED 4ft T5 Lamp 5K3L 25W LED 4ft T5 Lamp 5KLinear_LampMAXLITEL25T5DE450-CG
7MAXLITE Emergency battery Backup for Premium DownlightsEmergency battery Backup for Premium DownlightsAccessoryMAXLITEEBUUP15030N300
8MAXLITE Network Node - Rectangular WhiteNetwork Node - Rectangular WhiteControlMAXLITENN-RTW
9TBD 1/2-EMT Conduit 1/2-Inch x 10'1/2-EMT Conduit 1/2-Inch x 10'MaterialsTBD1/2-EMT
10UNITEDRENTALS Boom, Articulated, 30-39' Diesel DayBoom, Articulated, 30-39' Diesel DayRentalUNITED RENTALSBoom/39ft/Day
Fixtures (BOM)
Cell Formulas
RangeFormula
A3:A10A3=LET(sh,VSTACK('Line Item (input)'!W3:W502,'Line Item (input)'!Y3:Y502,'Line Item (input)'!AA3:AA502,'Line Item (input)'!AC3:AC502,'Line Item (input)'!AG3:AG502,'Line Item (input)'!AI3:AI502),SORT(UNIQUE(FILTER(sh,(sh <>"")*(sh<>0),""))))
B3:B10B3=IFERROR(RIGHT(A3,LEN(A3)-FIND(" ",A3)),"")
C3:C10C3=(XLOOKUP(A3,Tbl_Area[MaskedPartNumber],Tbl_Area[Product_Subgroup],XLOOKUP(A3,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[Product_Subgroup],XLOOKUP(A3,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[Product_Subgroup],XLOOKUP(A3,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[Product_Subgroup],XLOOKUP(A3,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[Product_Subgroup],XLOOKUP(A3,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[Product_Subgroup],XLOOKUP(A3,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[Product_Subgroup],XLOOKUP(A3,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[Product_Subgroup],XLOOKUP(A3,Tbl_Linear[MaskedPartNumber],Tbl_Linear[Product_Subgroup],XLOOKUP(A3,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[Product_Subgroup],XLOOKUP(A3,Tbl_Strip[MaskedPartNumber],Tbl_Strip[Product_Subgroup],XLOOKUP(A3,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[Product_Subgroup],XLOOKUP(A3,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[Product_Subgroup],XLOOKUP(A3,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[Product_Subgroup],XLOOKUP(A3,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[Product_Subgroup],XLOOKUP(A3,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Product_Subgroup],XLOOKUP(A3,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Product_Subgroup],XLOOKUP(A3,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Product_Type],XLOOKUP(A3,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Product_Type]))))))))))))))))))))))))))))))
D3:D10D3=(XLOOKUP(A3,Tbl_Area[MaskedPartNumber],Tbl_Area[Brand],XLOOKUP(A3,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[Brand],XLOOKUP(A3,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[Brand],XLOOKUP(A3,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[Brand],XLOOKUP(A3,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[Brand],XLOOKUP(A3,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[Brand],XLOOKUP(A3,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[Brand],XLOOKUP(A3,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[Brand],XLOOKUP(A3,Tbl_Linear[MaskedPartNumber],Tbl_Linear[Brand],XLOOKUP(A3,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[Brand],XLOOKUP(A3,Tbl_Strip[MaskedPartNumber],Tbl_Strip[Brand],XLOOKUP(A3,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[Brand],XLOOKUP(A3,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[Brand],XLOOKUP(A3,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[Brand],XLOOKUP(A3,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[Brand],XLOOKUP(A3,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[Brand],XLOOKUP(A3,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[Brand],XLOOKUP(A3,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[Brand],XLOOKUP(A3,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[Brand],XLOOKUP(A3,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[Brand],XLOOKUP(A3,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[Brand],XLOOKUP(A3,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[Brand],XLOOKUP(A3,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[Brand],XLOOKUP(A3,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[Brand],XLOOKUP(A3,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[Brand],XLOOKUP(A3,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Brand],XLOOKUP(A3,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Brand],XLOOKUP(A3,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Brand],XLOOKUP(A3,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Brand]))))))))))))))))))))))))))))))
E3:E10E3=(XLOOKUP(A3,Tbl_Area[MaskedPartNumber],Tbl_Area[PartNumber],XLOOKUP(A3,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[PartNumber],XLOOKUP(A3,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[PartNumber],XLOOKUP(A3,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[PartNumber],XLOOKUP(A3,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[PartNumber],XLOOKUP(A3,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[PartNumber],XLOOKUP(A3,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[PartNumber],XLOOKUP(A3,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[PartNumber],XLOOKUP(A3,Tbl_Linear[MaskedPartNumber],Tbl_Linear[PartNumber],XLOOKUP(A3,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[PartNumber],XLOOKUP(A3,Tbl_Strip[MaskedPartNumber],Tbl_Strip[PartNumber],XLOOKUP(A3,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[PartNumber],XLOOKUP(A3,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[PartNumber],XLOOKUP(A3,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[PartNumber],XLOOKUP(A3,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[PartNumber],XLOOKUP(A3,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[PartNumber],XLOOKUP(A3,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[PartNumber],XLOOKUP(A3,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[PartNumber],XLOOKUP(A3,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[PartNumber],XLOOKUP(A3,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[PartNumber],XLOOKUP(A3,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[PartNumber],XLOOKUP(A3,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[PartNumber],XLOOKUP(A3,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[PartNumber],XLOOKUP(A3,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[PartNumber],XLOOKUP(A3,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[PartNumber],XLOOKUP(A3,Tbl_Materials[MaskedPartNumber],Tbl_Materials[PartNumber],XLOOKUP(A3,Tbl_Rental[MaskedPartNumber],Tbl_Rental[PartNumber],XLOOKUP(A3,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[PartNumber],XLOOKUP(A3,Tbl_Controls[MaskedPartNumber],Tbl_Controls[PartNumber]))))))))))))))))))))))))))))))
Dynamic array formulas.


This is where all of the unnique values and quantities are coming from: Thjis sheet is titled: "Line Item (input)"

v2023 (version 1).xlsb
WXYZAAABACADAEAFAGAHAIAJ
2Fixture/LampQTY (fixture)Accessory 1QTY (Acc.1)Accessory 2QTY (Acc. 2)ControlQTY (Control)Add. LaborQty (Add. Labor)MaterialsQty (Materials)Rental EquipmentQTY (Rental)
3EIKO 1L 24W LED 4ft T5 Lamp 5K1HALCO Joiner Bracket for LWA Wrap1MAXLITE Emergency battery Backup for Premium Downlights1MAXLITE Network Node - Rectangular White1Cut Tiles1TBD 1/2-EMT Conduit 1/2-Inch x 10'1UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day1
4MAXLITE 3L 25W LED 4ft T5 Lamp 5K10HALCO Joiner Bracket for LWA Wrap1MAXLITE Emergency battery Backup for Premium Downlights1MAXLITE Network Node - Rectangular White1Cut Tiles1TBD 1/2-EMT Conduit 1/2-Inch x 10'1UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day1
5ENVISIONLED 8W LED Round Downlight [MCCT]1HALCO Joiner Bracket for LWA Wrap1MAXLITE Emergency battery Backup for Premium Downlights1MAXLITE Network Node - Rectangular White1Cut Tiles1TBD 1/2-EMT Conduit 1/2-Inch x 10'1UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day1
6ENVISIONLED 8W LED Round Downlight [MCCT]1HALCO Joiner Bracket for LWA Wrap1MAXLITE Emergency battery Backup for Premium Downlights1MAXLITE Network Node - Rectangular White1Cut Tiles1TBD 1/2-EMT Conduit 1/2-Inch x 10'1UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day1
7ENVISIONLED 8W LED Round Downlight [MCCT]1HALCO Joiner Bracket for LWA Wrap1MAXLITE Emergency battery Backup for Premium Downlights1MAXLITE Network Node - Rectangular White1Cut Tiles1TBD 1/2-EMT Conduit 1/2-Inch x 10'1UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day1
Line Item (input)


I am unsure as to whwere to even start. Any help would be greatly appreciated.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
The tables referenced in your formulas are not getting defined in the xl2bb paste. can you change those to range references (done in FILE >> OPTIONS, I think).
But actually you just need to post the critical columns are for lookup and target column to return a value from.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
UNIQUE FILTER giving me a blank row
Replies
5
Views
241
thewiseguy
T
T
  • Solved
VSTACK Causing "FALSE"
2 3 4
Replies
35
Views
1K
Fluff
Fluff
T
  • Question
Possible to Keep some cells visible while rows are hidden?
Replies
1
Views
220
6StringJazzer
6StringJazzer
T
  • Solved
Combining Data from Multiple Sheets
2
Replies
16
Views
846
alansidman
A

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top