Excel sum of multiple arrays
I have 2 tables (can be 100x100 table) in excel in the following sample format:
Table 1) shows consumption amount of certain food at certain age category
Table 2) Number of persons at certain age category
I want to find amount of each food type required each year based on number of persons without creating additional tables:
It is multiplication and summing array problem, but could not find solution for this problem using sum, sumproduct, or sumifs.. Does it exist?
The result should look like this:
Solution
Seems like MMULT with SUMIFS should do it:
=MMULT(SUMIFS(C2:C6,A2:A6,TOROW(A12:A14),B2:B6,A18:A20),--B12:D14)
- Get all first level keys of rows in a 2d array where a column value is partially matched by a search term
- Determine if a value exists in a 2d array
- Count the number of objects in an array of object with a specified property value
- Sum the correlated values of two array columns
- Count rows of a 2d array which contain a qualifying column value
- Splitting a Map instance into two separate arrays
- php - find if an array contains an element
- Find the column value of the first item in a multidimensional array with a sought value in a deep subarray
- How to pass an array argument to the Bash script
- Get a column of values from the specified subarray of a multidimensional array
- Search a 2d array for a qualifying column value and return its first level key
- Filter a 2d array to keep rows which have a specified column value
- Start from second item in forEach loop
- Malformed C/C++ Multidimensional Array Initialization in GCC
- Determine if all values in a flat array are null
- Will these two methods of accessing the array implemented identically or not?
- Converting ArrayList of Integer objects to an int array
- jQuery .map into array
- Why are arrays still in C# when we are actually always using List<T>?
- Determine if any row in a 2d array has a qualifying column value
- Populate a 2d array from a flat array and another 2d array
- Filter out phrases in a flat array of comma-separated values which do not contain all search terms
- Merge PHP const arrays to new const array without nesting
- Filter a 2d array to only keep rows with two specified column values
- Filter out elements of a flat array which do not contain all search terms
- Filter a 2d array by a flat whitelist array
- Get the key of the last qualifying value in an array
- Determine if all values in all rows of a query result set are null
- Search a 2d array for a row containing a qualifying column value and return its parent key
- Determine if qualifying values exist in an array