Home > Enterprise >  How to SUMIF identical data spread out on multiple columns
How to SUMIF identical data spread out on multiple columns

Time:08-05

I using excel to organize my cut list for a panel board. I have a table created like so, and i need help creating a formula that adds the number of pieces of panels of identical dimension and material, regardless of which panel it belongs

I have done formulas before where the datas where in a single column and there was only 1 criteria to check using UNIQUE and SUMIF formulas. I can already extract all the unique dimensions using

=UNIQUE(FLATTEN(C17:C19,E17:E19,G17:G19))

but that all that i got for now.

enter image description here

CodePudding user response:

try:

={"Material", "Dimension", "Pcs"; 
 QUERY({A2:C9; A2:A9, D2:E9; A2:A9, F2:G9}, 
 "select Col1,Col2,sum(Col3) where Col1 is not null 
  group by Col1,Col2 label sum(Col3)''", 0)}

enter image description here

  • Related