Conditionally Sum a googlesheet column based on entries in related tables
Say I have two related sheets/tabs within a google sheet. One sheet/tab is titled "Categories", the other is "Measures".
Categories:
| userid | catcode |
|---|---|
| 1 | a |
| 1 | b |
| 2 | a |
| 3 | c |
Measures:
| userid | catcode | points |
|---|---|---|
| 1 | a | 5 |
| 1 | b | 5 |
| 1 | c | 3 |
| 2 | a | 4 |
| 3 | c | 3 |
For each user I'd like to be able to sum the points from the Measures table where the catcode is present for the user in the categories table. Ideally using an auto-extending/filling formula (like an arrayformula or query).
I have some idea how I'd approach this with SQL statements (joining the related tables, or doing a select where exists), but I'm new to googlesheets and would appreciate some direction here. I've experimented with this a bit and assuming a third table named "Users" with userids in column A, I can add this formula:
=sum(filter(measure!C2:C4, measure!A2:A4=users!A2, not(iserror(vlookup(measure!B2:B4, unique(filter(categories!B2:B5, categories!A2:A5=users!A2)), 1, false)))))
However this approach doesn't seem to be compatible with arrayformula and won't allow me to autofill down the Users tab for newly added userids. Sum itself is apparently incompatible with arrayformula. Additionally, if I enclose the above in arrayformula and replace sum with sumproduct or some other approach to the summation, I'm unable to get the users!A2 references to extend down as I'd expect via something like users!A2:A.
Any help/direction would be appreciated. Thanks!
Try:
=ARRAYFORMULA(QUERY({A1:A, VLOOKUP(A1:A&B1:B, {D1:D&E1:E, F1:F}, 2, 0)},
"select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"))
- Displaying binary images on JSP page
- Linear search in a sorted array - Java
- Convert flat array of delimited key path strings to a multidimensional array with each path terminating at an empty array
- How to iterate through an array of pointer in c
- Is there a way to create dynamic ranges given an array of values, and counting how many belong to each range
- Given an array of N integers, return the max sum of 3 NON-adjacent elements
- Parse array of strings composed of dot.separated.keys into a multidimensional array
- Explode each string in a flat array
- Parse a string with three delimiters into an associative array of indexed arrays
- Paginate Javascript array
- Page through an array
- Sum values based on three criteria and transpose result from tabular to visual layout
- Convert a JavaScript statement to a string
- Sort an array of associative arrays by column value
- Parse a multiline string with two delimiters per line into an array of associative rows
- Convert a string with 2 delimiters into a flat associative array
- How do I Split and filter 2 arrays
- PHP Array to List only values
- How to join / combine two arrays to concatenate into one array?
- Split a string by <br> tags wrapped in newlines
- Add prefix to array elements then join into a string
- Parse square brace tagged text into an associative array
- Parse an SQL CALL statement and create an array with its parameters
- How to insert an item at the beginning of an array in PHP?
- Parse inline style declaration string to array
- How to swap the positions of Min and Max in an array?
- Combining two arrays to form a JavaScript object
- Cannot memory-free strings from arbitrary-length array of strings
- Is a char array more efficient than a char pointer in C?
- Split the comma-separated string of a specified column in each row of a multidimensional array