Good Day to all!
Is there any possibility to use the SUMIF function along with AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want SUMIF to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Good Day to all!
Is there any possibility to use the SUMIF function along with AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities

When selecting, for example, a client name with the AutoFilter, I want SUMIF to give me the quantity of pineaples, for example.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hi

Look at worksheet function SUBTOTTAL in Excel Help


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



"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hi,
Have looked there, however I can't use SUMIF with SUBTOTAL.
Any other clue?


"Arvi Laanemets" escreveu na mensagem ...
Hi

Look at worksheet function SUBTOTTAL in Excel Help


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



"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Something like

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))*(C2:C20="N
o"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hi

If you have already applied your Filter for client, then
=SUBTOTAL(9,C2:C1000)
will Total all Fruits for him.
Using the dropdown on Fruit, to select Apples, will give the total
Apples for that client, changing the selection to Oranges will produce
that total.

If you are wanting to see the total for each fruit for a client, then in
cells E1:G1 enter Apples, Oranges Lemons
In cell D2 enter the Client Name
in cell E2 enter
=IF(D2="","",
SUMPRODUCT(($A$2:$A$1000=$D2)*
($B$2:$B$1000=E$1)*($C$2:$C$1000))

Copy across through cells F2:G2
If you copy E2:G2 down for as many rows as required, then having each
client listed in column D will give the analysis for all of them

Better still, use a Pivot Table to summarise all of the data for you.

Mark the block of data, then DataPivot TablesFinish
Drag Customer to the Row area
Drag Fruit to the Column area
Drag Quantity to the Data Area.

For more help on Pivot Tables take a look at
Take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx


--
Regards

Roger Govier


"Gustavo Strabeli" wrote in message
...
Hi,
Have looked there, however I can't use SUMIF with SUBTOTAL.
Any other clue?


"Arvi Laanemets" escreveu na mensagem
...
Hi

Look at worksheet function SUBTOTTAL in Excel Help


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



"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with
AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want
SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hi, Roger!
The second option (IF function) gives the desired result, but in fact not what I was expecting. It's almost it.
The point is that I have to use the AutoFilter on this spreadsheet.

Example:
E1:G1 - Fuits (Apples, Oranges, Lemons)
E2:G2 - Need a function to show the quantities of fruits when I select each client with the AutoFilter.

In other words, if I select client "A" with the AutoFilter, in cell E2 I want the quantity of Apples.

Any chance?

Thanks.


"Roger Govier" escreveu na mensagem ...
Hi

If you have already applied your Filter for client, then
=SUBTOTAL(9,C2:C1000)
will Total all Fruits for him.
Using the dropdown on Fruit, to select Apples, will give the total
Apples for that client, changing the selection to Oranges will produce
that total.

If you are wanting to see the total for each fruit for a client, then in
cells E1:G1 enter Apples, Oranges Lemons
In cell D2 enter the Client Name
in cell E2 enter
=IF(D2="","",
SUMPRODUCT(($A$2:$A$1000=$D2)*
($B$2:$B$1000=E$1)*($C$2:$C$1000))

Copy across through cells F2:G2
If you copy E2:G2 down for as many rows as required, then having each
client listed in column D will give the analysis for all of them

Better still, use a Pivot Table to summarise all of the data for you.

Mark the block of data, then DataPivot TablesFinish
Drag Customer to the Row area
Drag Fruit to the Column area
Drag Quantity to the Data Area.

For more help on Pivot Tables take a look at
Take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx


--
Regards

Roger Govier


"Gustavo Strabeli" wrote in message
...
Hi,
Have looked there, however I can't use SUMIF with SUBTOTAL.
Any other clue?


"Arvi Laanemets" escreveu na mensagem
...
Hi

Look at worksheet function SUBTOTTAL in Excel Help


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



"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with
AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want
SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hey, Bob!
This function gives "0" as result...

"Bob Phillips" escreveu na mensagem ...
Something like

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))*(C2:C20="N
o"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Can you give some data details and your actual formula.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Gustavo Strabeli" wrote in message
...
Hey, Bob!
This function gives "0" as result...

"Bob Phillips" escreveu na mensagem
...
Something like

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))*(C2:C20="N
o"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hi Gustavo

The problem is that when you use Autofilter, we don't know which will be
the first visible cell in Column A (without writing VBA code) hence we
cannot use that cell in place of D2.

You could create a list of Client names on Sheet 2.
Mark column ADataFilterAdvanced FilterCopy to another location
Sheet2!A1Unique values
Mark the new block of names on Sheet2 and in the Name box (left of
Column A) type Mynames and press Enter.

If you went to D2 and did DataValidationList =Mynames
Then use the dropdown on D2 to select the Client and you will get the
required result.

--
Regards

Roger Govier


"Gustavo Strabeli" wrote in message
...
Hi, Roger!
The second option (IF function) gives the desired result, but in fact
not what I was expecting. It's almost it.
The point is that I have to use the AutoFilter on this spreadsheet.

Example:
E1:G1 - Fuits (Apples, Oranges, Lemons)
E2:G2 - Need a function to show the quantities of fruits when I select
each client with the AutoFilter.

In other words, if I select client "A" with the AutoFilter, in cell E2 I
want the quantity of Apples.

Any chance?

Thanks.


"Roger Govier" escreveu na mensagem
...
Hi

If you have already applied your Filter for client, then
=SUBTOTAL(9,C2:C1000)
will Total all Fruits for him.
Using the dropdown on Fruit, to select Apples, will give the total
Apples for that client, changing the selection to Oranges will produce
that total.

If you are wanting to see the total for each fruit for a client, then in
cells E1:G1 enter Apples, Oranges Lemons
In cell D2 enter the Client Name
in cell E2 enter
=IF(D2="","",
SUMPRODUCT(($A$2:$A$1000=$D2)*
($B$2:$B$1000=E$1)*($C$2:$C$1000))

Copy across through cells F2:G2
If you copy E2:G2 down for as many rows as required, then having each
client listed in column D will give the analysis for all of them

Better still, use a Pivot Table to summarise all of the data for you.

Mark the block of data, then DataPivot TablesFinish
Drag Customer to the Row area
Drag Fruit to the Column area
Drag Quantity to the Data Area.

For more help on Pivot Tables take a look at
Take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx


--
Regards

Roger Govier


"Gustavo Strabeli" wrote in message
...
Hi,
Have looked there, however I can't use SUMIF with SUBTOTAL.
Any other clue?


"Arvi Laanemets" escreveu na mensagem
...
Hi

Look at worksheet function SUBTOTTAL in Excel Help


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



"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with
AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want
SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hi Gustavo,

without acrobatics, place this basic excel formula from help files

=subtotal(109,"your column of quantities")

this will give the sum of unhidden rows and it update based on your
autofiltering of client name or specific Fruits

"Gustavo Strabeli" wrote:

Good Day to all!
Is there any possibility to use the SUMIF function along with AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want SUMIF to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

According to one of the posts, the fruit is in Col B and the Quantity is in
Col C. I think he just needs to swap the column references in your formula.

=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$1,ROW($C$2:$C$20)-ROW($C$1),,1))*(B2:B20="Apple"))

Although, perhaps the OP could apply a second filter to the fruit column and
use
=Subtotal(9, C2:C20)


"Bob Phillips" wrote:

Can you give some data details and your actual formula.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Gustavo Strabeli" wrote in message
...
Hey, Bob!
This function gives "0" as result...

"Bob Phillips" escreveu na mensagem
...
Something like

=SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))*(C2:C20="N
o"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hi

Place column headings into row 3 (i.e. E3:G3 - Fuits (Apples, Oranges,
Lemons)
Start your data from row 4
Define (InsertNameDefine) a named range p.e. MyTable
=OFFSET(YourSheetName!$A$3,1,,COUNTA(YourSheetName !$A:$A)-1,7)
(replace YourSheetName with real sheet name of-course)
(NB! Be sure there never will be anything in column A except header in A3
and client names, and never will be anything in row 2. And there never will
be gaps in client names too.)

Into D1, enter 'Filtered amount:'
Into E1, enter the formula
=SUBTOTAL(3,INDEX(MyTable,,COLUMN()))
Copy E1 to range E1:G1
Select any cell from your table, and activate autofilter for your table (as
row2 is empty, the autofilter applies to your table only)
Set the filter.
Its all in general. But maybe you select cell A4 and use Freeze Panes
feature , to keep headings and sums always visible.


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


"Gustavo Strabeli" wrote in message
...
Hi, Roger!
The second option (IF function) gives the desired result, but in fact not
what I was expecting. It's almost it.
The point is that I have to use the AutoFilter on this spreadsheet.

Example:
E1:G1 - Fuits (Apples, Oranges, Lemons)
E2:G2 - Need a function to show the quantities of fruits when I select each
client with the AutoFilter.

In other words, if I select client "A" with the AutoFilter, in cell E2 I
want the quantity of Apples.

Any chance?

Thanks.


"Roger Govier" escreveu na mensagem
...
Hi

If you have already applied your Filter for client, then
=SUBTOTAL(9,C2:C1000)
will Total all Fruits for him.
Using the dropdown on Fruit, to select Apples, will give the total
Apples for that client, changing the selection to Oranges will produce
that total.

If you are wanting to see the total for each fruit for a client, then in
cells E1:G1 enter Apples, Oranges Lemons
In cell D2 enter the Client Name
in cell E2 enter
=IF(D2="","",
SUMPRODUCT(($A$2:$A$1000=$D2)*
($B$2:$B$1000=E$1)*($C$2:$C$1000))

Copy across through cells F2:G2
If you copy E2:G2 down for as many rows as required, then having each
client listed in column D will give the analysis for all of them

Better still, use a Pivot Table to summarise all of the data for you.

Mark the block of data, then DataPivot TablesFinish
Drag Customer to the Row area
Drag Fruit to the Column area
Drag Quantity to the Data Area.

For more help on Pivot Tables take a look at
Take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx


--
Regards

Roger Govier


"Gustavo Strabeli" wrote in message
...
Hi,
Have looked there, however I can't use SUMIF with SUBTOTAL.
Any other clue?


"Arvi Laanemets" escreveu na mensagem
...
Hi

Look at worksheet function SUBTOTTAL in Excel Help


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



"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with
AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want
SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hi Arvi

I don't think that works, as the type for each fruit are all held in the
one column.
But following your methodology for layout and having the data starting
in row 4 with headings as you describe, then a modification of Bob's
formula entered in E1 and copied to F1:G1 does give the correct result.
In E1
=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$3,ROW($C$3:$C$30)
-ROW($C$3),,1))*($B$3:$B$30=E$3))

Amend the length of the ranges to suit the size of the data table

--
Regards

Roger Govier


"Arvi Laanemets" wrote in message
...
Hi

Place column headings into row 3 (i.e. E3:G3 - Fuits (Apples, Oranges,
Lemons)
Start your data from row 4
Define (InsertNameDefine) a named range p.e. MyTable
=OFFSET(YourSheetName!$A$3,1,,COUNTA(YourSheetName !$A:$A)-1,7)
(replace YourSheetName with real sheet name of-course)
(NB! Be sure there never will be anything in column A except header in
A3 and client names, and never will be anything in row 2. And there
never will be gaps in client names too.)

Into D1, enter 'Filtered amount:'
Into E1, enter the formula
=SUBTOTAL(3,INDEX(MyTable,,COLUMN()))
Copy E1 to range E1:G1
Select any cell from your table, and activate autofilter for your
table (as row2 is empty, the autofilter applies to your table only)
Set the filter.
Its all in general. But maybe you select cell A4 and use Freeze Panes
feature , to keep headings and sums always visible.


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


"Gustavo Strabeli" wrote in message
...
Hi, Roger!
The second option (IF function) gives the desired result, but in fact
not what I was expecting. It's almost it.
The point is that I have to use the AutoFilter on this spreadsheet.

Example:
E1:G1 - Fuits (Apples, Oranges, Lemons)
E2:G2 - Need a function to show the quantities of fruits when I select
each client with the AutoFilter.

In other words, if I select client "A" with the AutoFilter, in cell E2
I want the quantity of Apples.

Any chance?

Thanks.


"Roger Govier" escreveu na mensagem
...
Hi

If you have already applied your Filter for client, then
=SUBTOTAL(9,C2:C1000)
will Total all Fruits for him.
Using the dropdown on Fruit, to select Apples, will give the total
Apples for that client, changing the selection to Oranges will produce
that total.

If you are wanting to see the total for each fruit for a client, then
in
cells E1:G1 enter Apples, Oranges Lemons
In cell D2 enter the Client Name
in cell E2 enter
=IF(D2="","",
SUMPRODUCT(($A$2:$A$1000=$D2)*
($B$2:$B$1000=E$1)*($C$2:$C$1000))

Copy across through cells F2:G2
If you copy E2:G2 down for as many rows as required, then having each
client listed in column D will give the analysis for all of them

Better still, use a Pivot Table to summarise all of the data for you.

Mark the block of data, then DataPivot TablesFinish
Drag Customer to the Row area
Drag Fruit to the Column area
Drag Quantity to the Data Area.

For more help on Pivot Tables take a look at
Take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx


--
Regards

Roger Govier


"Gustavo Strabeli" wrote in message
...
Hi,
Have looked there, however I can't use SUMIF with SUBTOTAL.
Any other clue?


"Arvi Laanemets" escreveu na mensagem
...
Hi

Look at worksheet function SUBTOTTAL in Excel Help


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



"Gustavo Strabeli" wrote in message
...
Good Day to all!
Is there any possibility to use the SUMIF function along with
AutoFilter?
I have the following situation:

Column A: Clients' names
Column B: Fruits
Column C: Quantities


When selecting, for example, a client name with the AutoFilter, I want
SUMIF
to give me the quantity of fruits.
It's like a subtotal function, however for the SUMIF.

Any chance to do it?

Thanks a lot,
Gustavo.

Hi


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

I don't think that works, as the type for each fruit are all held in the
one column.

E1:G1 - Fuits (Apples, Oranges, Lemons)

It looks like I was right and you wrong at it :-)))

E2:G2 - Need a function to show the quantities of fruits when I select
each client with the AutoFilter.

Here I moved the placement of formulas at top of page

In other words, if I select client "A" with the AutoFilter, in cell E2 I
want the quantity of Apples.

.... and in cell F2 the quantity of oranges etc. ...


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

Hi Arvi

I agree the OP wants to see the results in individual columns, but the
source data is in a single column.
Your formula
=SUBTOTAL(3,INDEX(MyTable,,COLUMN()))

will give a Count (not a Sum)<bg
but the column returned when the formula is in cell E1 will be 5 and
since Mytable is set to A:G it will perform the calculation on column E
which has no data.
When copied to F1 will be 6 etc.

The values to be Summed, are all in column C

--
Regards

Roger Govier


"Arvi Laanemets" wrote in message
...
Hi


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

I don't think that works, as the type for each fruit are all held in
the one column.

E1:G1 - Fuits (Apples, Oranges, Lemons)

It looks like I was right and you wrong at it :-)))

E2:G2 - Need a function to show the quantities of fruits when I
select
each client with the AutoFilter.

Here I moved the placement of formulas at top of page

In other words, if I select client "A" with the AutoFilter, in cell
E2 I want the quantity of Apples.

... and in cell F2 the quantity of oranges etc. ...


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

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

Select A4, and apply autofilter
Set filter to table.



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

"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 )