Google Sheets Map LAMBDA function
I have the following data.
I need a formula to calculate how many complete shots have been completed by Dominic for example. I have the following formula:
=SUM(MAP('Shot List'!A:A,'Shot List'!D:D,'Shot List'!H:H,LAMBDA(name,shots,complete,IF(name=B7,SUMPRODUCT(shots,complete=TRUE),shots))))
Where A:A is the Sim Pilot, D:D is the number of shots and H:H is whether that shot has been completed. The closest I've got is to count the total number of unchecked boxes for "Dominic" but as you can see the first number is not being counted or rather calculated? Any help would be greatly appreciated. Thanks.
Solution
Use query(), like this:
=query('Shot List'!A3:H, "select A, sum(D) where H = true group by A", 0)
The formula will get the total number of shots for all names that have completed at least one shot.
See query().
- Google Sheets Map LAMBDA function
- Safe method to get value of nested dictionary
- Fastest way of deleting certain keys from dict in Python
- How to convert a nested Python dict to object?
- Accessing dict_keys element by index in Python3
- Difference between HashMap, LinkedHashMap and TreeMap
- Insert a list of dictionary using sqlalchemy efficiently
- Getting lowest non-zero value from a dictionary
- Removing elements based on nested dictionary values
- Set to dict Python
- Python program to combine two dictionary adding values for common keys
- Processing Dictionary inside JSON
- Set a value in a dict only if the value is not already set
- Dictionaries, new dictionary typing, and their operating speeds
- How to check if one dictionary is a subset of another larger dictionary?
- storing and retrieving images in dictionary - python
- Can you update ansible dictionary on the fly?
- How do I map a dictionary using Fluent NHibernate automapping?
- Delete keys from dictionary based on condition in python
- Modify a Python "requests" module response object
- Moving elements of a python dictionary to another one under certain condition using less code
- How do I merge two dictionaries in a single expression in Python?
- Difference between defining typing.Dict and dict?
- Convert a python dict to a string and back
- Remap values in pandas column with a dict, preserve NaNs
- Can I index the keys of a object-type subdocument in MongoDB?
- What data structure do Go maps use internally?
- Create dictionary with alphabet characters mapping to numbers
- Why is using a dictionary slower than sorting a list to generate frequency array?
- Creating a dictionary that writes high scores to a file