Return a Two Dimensional Array using a Sum Product Formula (Excel)
I am looking to return a two-dimensional array using a sum product formula. I have a table full of values which contain two axis (see attached image for example)
I want to return a dynamic array which sums the values by the two axis. In this example, the returned table would have three columns corresponding to the unique axis values of the original table (Alpha, Beta, Delta), and 6 rows.
The typical way to sum a two dimensional array based on criteria is with a =SUMPRODUCT function. This function returns the sum based on the values of the two axis, however, I have not found a way to return a dynamic array using this formula.
Solution
Perhaps you could try using MMULT() function:
=UNIQUE(HSTACK(A1:A7,VSTACK(B1:H1,MMULT(B2:H7,N(B1:H1=TOCOL(B1:H1))))),1)
Using LET() to avoid repeating same ranges with variables:
=LET(a, B1:H1, UNIQUE(HSTACK(A1:A7,VSTACK(a,MMULT(B2:H7,N(a=TOCOL(a))))),1))
- Merge two different array
- Merge two indexed arrays without overwriting elements in first array
- Merge items from two multidimensional arrays related by first level indexes
- Checking if an object is array-like
- Searching a record which has been put into an array of records in Delphi/Lazarus
- filter out object in array if object key value is null
- Group rows of a 2d array by a column and push rows as subarrays of each group
- Why can Array.prototype methods be used with non-array objects?
- Group 2d array rows by one column and sum another column within each group
- Adding Elements to a KeyValuePair<X, Y>
- undefined name "array" while processing JSON message via AVRO Schema
- Overwrite row data in a 2d array when related data is found in another 2d array
- Merge two flat arrays in an alternating fashion
- combine php array, with matching values
- Array Match & Add Values to First array
- Merge multple 2d associative arrays into one consolidated 2d associative array
- How to change numpy array into grayscale opencv image
- Find the Smallest Integer Not in a List
- JS - Get top 5 max elements from array
- Sorting an array of objects by property values
- How to get only the first occurrence of each increasing value in numpy array?
- Replace row data in a 2d array if identifying column matches new row data
- Merging two flat arrays by keys into an associative array
- Recursively merge two multidimensional associative arrays and define new subarray keys according to their origin array
- JavaScript: multiple filters chain vs. complex condition
- Why do I get an "Array not defined" reference error with this code?
- Merge/flatten an array of arrays
- How to remove unwanted comma (,) from insertAdjacentHTML object in Javascript?
- Django request.GET adds and extra quote to the data
- React useContext(): Objects are not valid as a React child when trying to execute function in useContext() api to fetch data