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.
A few more array options to experiment with:
=MMULT(--(TOROW(Table2[Date]) = Table3[Date]), (XLOOKUP(Table2[Item Code], Table1[Item Code], Table1[Category], "") = Table3[[#Headers],[Blue]:[Red]]) * Table2[Units])Complete with column headers and row labels:
=LET( criteria, DROP(Table3[#Headers],, 1), category, XLOOKUP(Table2[Item Code], Table1[Item Code], Table1[Category], ""), result, MMULT(--(TOROW(Table2[Date]) = Table3[Date]), (category = criteria) * Table2[Units]), HSTACK(Table3[[#All],[Date]], VSTACK(criteria, result)) )Alternatively, using SUM-SUMIFS with "thunks" (LAMBDA(x)) and broadcasting:
=LET( grϑup, TRANSPOSE(GROUPBY(Table1[Category], Table1[Item Code], LAMBDA(x, LAMBDA(x)), 0, 0)), bcλst, LAMBDA(b, IF(b, Table3[Date], TAKE(grϑup, -1))), result, MAP(bcλst({0}), bcλst({1}), LAMBDA(c,d, SUM(SUMIFS(Table2[Units], Table2[Date], d, Table2[Item Code], c())))), HSTACK(Table3[[#All],[Date]], VSTACK(TAKE(grϑup, 1), result)) )There's also a basic SUMIFS example in the attached file (on the Production worksheet), using a helper column for "Category"...
If you are on Windows, you may add both table to data model, create relationship
and generate PivotTable
Or you may generate Power Query
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], DeclareType = Table.TransformColumnTypes(Source,{{"Date", type date}}), MergeTable1 = Table.NestedJoin(DeclareType, {"Item Code"}, Table1, {"Item Code"}, "Table1", JoinKind.LeftOuter), ExpandTable1 = Table.ExpandTableColumn(MergeTable1, "Table1", {"Category"}, {"Category"}), SelectColumns = Table.SelectColumns(ExpandTable1,{"Date", "Category", "Units"}), PivotColumn = Table.Pivot(SelectColumns, List.Distinct(SelectColumns[Category]), "Category", "Units", List.Sum) in PivotColumnwhich returns
We used to use PowerQuery, but the person that was in charge of this kept forgetting to update it. I know we can automate the tasks, but we rather reduce the number of potential failure points. That's why we're doing a dynamic array. The only necessary part is the manual data entry.
If something breaks, it's also much easier to track down. I'm not working with tech savvy people :/.
I see. Yes, with PowerQuery is headache with refreshing. But with dynamic arrays is headache with formatting.
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) )Hey Patrick2788,
Tried this after modifying it to work with my real workbook. I see a few errors in the output.
- Figured out what the first column was. It was just the dates in a different format. Figured it out after writing this.
- The second column, which is the first one with a header name, seems to be displaced by 1 row when compared to the rest.
- The fourth and fifth columns (this one is titled just "0") should be added together in a single column.
- While I see what you were going for with the format, I actually do need a way to do each column separately since I need to do extra columns with different titles using VSTACK. With your method, I'm stuck with using the headers that are automatically done with the code. Is there an alternative?
I'll keep testing it out tomorrow, but thanks for something that does get close to what I'm looking for.
If you're able to share an anonymized sample workbook, I can take a look.
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#
Thanks for replying, HansVogelaar
I'm trying to edit the code into my real workbook. However, I'm simply getting blank results instead of a spilled array.
Any idea what may be wrong? Any information you need on my behalf?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
