Hi
Place column headings into row 3 (i.e. E3:G3 - Fuits (Apples, Oranges,
Lemons)
Start your data from row 4
Define (Insert>Name>Define) 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<at>tarkon.ee )
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" <
[email protected]> 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 Data>Pivot Tables>Finish
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/flashfiles/pivot1.html
http://www.edferrero.com/Tutorials.aspx
--
Regards
Roger Govier
Hi,
Have looked there, however I can't use SUMIF with SUBTOTAL.
Any other clue?
"Arvi Laanemets" <
[email protected]> escreveu na mensagem
Hi
Look at worksheet function SUBTOTTAL in Excel Help
--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )
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.