"Arvi Laanemets" wrote in message
...
Hi

"Roger Govier" wrote in message
...
Hi Arvi

I agree the OP wants to see the results in individual columns, but the
source data is in a single column.

I see it now, after I did read earlier postings from OP too.

Your formula
=SUBTOTAL(3,INDEX(MyTable,,COLUMN()))

will give a Count (not a Sum)<bg

My flop there, it must be 9 as 1st parameter of-course!


Let's modify my setup a bit.

A4='Client'
B4='Fruit'
C4=Quantity

Add a new sheet Fruits, with a table
Fruit
Apples
Oranges
Lemons
etc. ...

Create a named range Fruits
=OFFSET(List!$A$1,1,,COUNTA(List!$A:$A)-1,1)

On a data sheet, OP can now apply a data validation list for column B - so
only fruits from List can be added, and user can select them from
dropdown.
Let's assume, that there may be no more than 10 different fruits at all -
otherwise the design must be changed.

D3=IF(ISERROR(INDEX(List,COLUMN()-3)),"",INDEX(List,COLUMN()-3))
and copy to range D4:M4

D4=IF(OR($A5="",$B5="",$C5="",D$4=""),"",($B5=D$4) *$C5)
Copy D4 to range D5:M#, where # represents any row number OP thinks as
reasonable.
Hide columns D:M

Create a named range Data (instead of MyTable, and let's the sheet with
data be Data too)
=OFFSET(Data!$D$4,1,,COUNTA(Data!$A:$A)-1,10)

N1=IF(ISERROR(INDEX(List,COLUMN()-13)),"",INDEX(List,COLUMN()-13))
N2=IF(N1="","",SUBTOTAL(9,INDEX(Data,,COLUMN()-13)))
Copy N1:N2 to range N1:X2

On second thougth, why bother with sums in header at all. OP can use the
sheet Fruits, and calculate sums there, i.e.
Fruits!B1='Filtered Amount'
Fruits!B2=IF(A2="","",SUBTOTAL(9,INDEX(Data,,ROW()-1)))
Copy B2 down.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )