Forum Discussion
Alternate to SUMIFS array assistance
Hello, everyone.
I apologize for asking yet another SUMIFS question, but I haven't been able to find a thread anywhere (so far) that offers a sample similar to what I'm trying to do.
I have a spreadsheet with 3 tables located in separate sheets: SKU, Production, and Expected.
In the SKU table I have a list of item codes that are part of a general Category.
In the Production table, I pretty much have a manual log of the number of Units that I produced in different Dates for each Item Code that applies.
In the final table I pretty much just have a table with the Dates. The file I uploaded has a total of 4 columns in this table, but in my actual document the last 3 columns are actually arrays. I just placed them inside the table for purposes of this example. Anyhow, columns B-D are the Categories each Item Code belongs to: Blue, Green, or Red.
What I'm trying to do is as follows: I'm trying to do the equivalent of a SUMIFs formula for Blue, Green, and Red. It first checks that the date in the Expected sheet matches the date in the Production sheet. Next, it checks in the SKU table which Category the Item Code belongs to for the selected date. It then adds the total Units for each Category column.
I've tried with variations found online of SUMIFs and SUMPRODUCT, but I haven't been able to make them work.
Any and all help is greatly appreciated.
With dynamic arrays, you could use:
=LET( category, XLOOKUP(Table2[Item Code], Table1[Item Code], Table1[Category], "None"), PIVOTBY(Table2[Date], category, Table2[Units], SUM, , 0, , 0) )In B2:
=SUMPRODUCT(Table2[Units], (Table2[Date]=[@Date])*(XLOOKUP(Table2[Item Code], Table1[Item Code], Table1[Category])=Table3[[#Headers],[Blue]]))
In your real workbook, try replacing Table3[[#Headers],[Blue]] with B2#
