How to create a formula that can get me the distinct name list from the left table then get their max values?
Please help me
To get the list, you are going to want to use two helper columns. The first I will call "Count" and the second I will call "Number".
=IF(COUNTIFS([List of Vehicles]$1:[List of Vehicles]@row, [List of Vehicles]@row) = 1, COUNT(DISTINCT([List of Vehicles]$1:[List of Vehicles]@row)))
This will number each of the distinct values at their first entry in the [List of Vehicles] column. Using your above screenshot as an example, it should look something like:
Then in the Number column, you would manually enter
Your [Distinct name] column would then contain the formula of
=INDEX([List of Vehicles]:[List of Vehicles], MATCH([email protected], Count:Count, 0))
Dragfill that down, and you should be set. You will need to manually add more numbers in the Number column as new vehicle types are added.
You could pre-fill additional numbers to anticipate additional vehicle types and wrap the INDEX/MATCH in an IFERROR to leave those cells blank until they are needed.
=IFERROR(INDEX([List of Vehicles]:[List of Vehicles], MATCH([email protected], Count:Count, 0)), "")
So if the setup is
Your screenshot data would show
Put this formula into each cell in Max Number column
=MAX(COLLECT(Number:Number, [List of Vehicles]:[List of Vehicles], [Distinct name]@row))
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
@Frank Falco Thank you for the formula! As an add on, I converted MAX to SUM, and added an IF logic as a prefix, to only add totals where the Distinct Name was completed in the summary table