I am trying to summarise filtered data in a simple table for charting. I have hit two walls.
Using the aggregate function I have created this table as you see attached,
=AGGREGATE(16,3,INDIRECT("Data"&"["&$A60&"]"),$A$1)
Wall 1; It took 3 tables to create. The user must filter each table exactly right to compare Christchurch sales over the three years.
Similarly I have achieved the same result with an array formula from one table
{=PERCENTILE.INC(IF(Data[Year]=C56,IF(INDIRECT("Data"&"["&A60&"]")<>0,IF(INDIRECT("Data"&"["&D4&"]")=D5,INDIRECT("Data"&"["&$A60&"]"),""),""),""),$A$1)}
This formula filters for [year], 0 values in the target field and filters for one other criterion.
Wall 2; If I want all data for the year this formula fails, it's like I have to know in advance how many criteria will be required for filtering.
The user needs the ability to filter for 0 to 3 criteria other than [year] which needs to be filtered for the column.
Hi Carl,
Without a sample file with data, there are small changes to receive an answer, nobody can test a solution with unknown data structure.
There can be many ways to do this, maybe better then what you did, but we need to see some sample data.
