Forum

You could extract the max year with: =MAX(INDEX(("0"&MID(B2,(ROW($1:$10)-1)*5+1,4))+0,)) into say C2 (assuming you never have more than 10 y...

What if it's not greater than them all, less than them all, or equal to any of them? Eg you have 2017 in col A and 2016|2019 in column B?

Or perhaps: =MAX(SUMPRODUCT(B3:B113,K135:K245),0)*SUM(1,SUMPRODUCT(B98:B133,K247:K282))

I'd suggest you add a new column with the IT (and other) identifiers in it, and then use SUMIF formulas.

It returns an array, so you either need to array enter it into a range of cells at once, or use INDEX with it: =INDEX(named_range,1) for ex...

You could do something like this, using Table.Schema in a function.

Can you explain what the resulting table should look like, and why?

Here you are. I just added "%" to each header.

You could add a column using a formula like: Value.Is([field name], type datetime) to return True for the dates and False otherwise. You c...

It would be really easy if you could add an identifier to your column headers - eg use "Supports mounted %" as the header. You can then use: =SUMIF...

It sounds to me like using a pivot table with a distinct count would do what you need without any need to create this specific column in the source da...

It's always the little things that are the easiest to overlook. 🙂

Did you change the listbox's ColumnCount property to 7?

It looks like you just need to pivot on the RunDate column using Amount as the Value field.

You could try checking the Fast Data Load option to see if that helps?