arraysgoogle-sheetssumvlookupgoogle-query-language
How to create a total quantity based upon multiple criteria from 2 arrays
What I'm trying to accomplish is going to be illustrated from the below picture, the example spreadsheet is linked as well.
I need to create function that will sum Quantity Columns G & D where ID Columns B & E is less than 5000. Then display those results to the corresponding ID in Column J.
Lastly, the quantities for the Materials in the Chain Material section need to carry over to the respective ID in Column H.
I do NOT need to sum anything from Column D where the ID from Column B is greater than 5000, that information is useless.
Expected result can be seen next to each cell in Column K.
Thank you in advance!
https://docs.google.com/spreadsheets/d/1seoOaGytQ8wKH-wXR7hhN1YGdWiNNNhMIyIpCzRAMG4/edit?usp=sharing
Solution
use in J4:
=INDEX(IFNA(VLOOKUP(H4:H, QUERY({B4:D; E4:G},
"select Col1,sum(Col3) group by Col1"), 2, 0)))
- How to use Array.from with a XPathResult?
- WooCommerce Orders and Items Array with JQuery DataTables
- Printing out a 2D array in matrix format
- Are arrays treated as static data structures by gcc compiler or as dynamic?
- What is the purpose of a LinkedList in Java Considering an ArrayList Has No Size Limit?
- How to check if any word from a list is in a string as a whole word (i.e. not as part of another word)?
- check if all elements of an array have the same value in Swift
- Initializing a huge array (50000 entries)
- How to store SQL query result into variable to include in a later SELECT statement
- Perl calculate average from randomly populated array
- Are there any performance advantages to use `Application.Match` on a VBA array instead of looping over it to check if an element is present or not?
- Combine two streams to display as one in GUI
- How to convert uint8 Array to base64 Encoded String?
- Json Object Parsing using shell script
- How to initialize array to tuple type in TypeScript?
- Is there a way in C++ to get a sub array from an array?
- How can I use in_array if the needle is an array?
- How to sort my array by length of its keys array in javascript?
- How to save a value to a file after a person has entered the value in terminal in C?
- C++ the dynamic array and file reading don't work together
- How to change the name of an array using a function to access a totally different array in Arduino / C/ C++
- How to get a slice as an array in Rust?
- How to merge two array of object into one array of object based on key?
- How to destructure this Array
- How to check if two indexed arrays have same values even if the order is not same?
- Reverse an array in JavaScript without mutating the original array
- PHP create array group by key and value
- Is there an algorithm for shuffling objects in an array randomly WITHOUT utilizing Lists and Collections
- accessing array and objects
- How to recursively merge two multidimensional arrays