SUMIFS or SUMPRODUCT?

T

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
1,015
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
  • Solved
SUMPRODUCT formula not working
Replies
5
Views
246
thewiseguy
T
T
  • Question Question
Is It A Problem to Have Your Table Contain Reference Cells?
Replies
5
Views
265
My Aswer Is This
My Aswer Is This
T
  • Question Question
UNIQUE FILTER giving me a blank row
Replies
5
Views
384
thewiseguy
T
T
  • Question Question
Substitute for a LARGE xlookup formula?
Replies
5
Views
281
Cubist
Cubist
T
  • Solved
VSTACK Causing "FALSE"
2 3 4
Replies
35
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,223,867
Messages
6,175,063
Members
452,611
Latest member
bls2024

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