SUMIF with AutoFilter

[Gustavo Strabeli]

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.

 

[Gustavo Strabeli]

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.

 

[Arvi Laanemets]

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.

 

[Gustavo Strabeli]

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.

 

[Bob Phillips]

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)

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.

 

[Roger Govier]

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.

 

[Gustavo Strabeli]

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.

 

[Gustavo Strabeli]

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

"Bob Phillips" <[email protected]> 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)

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.

 

[Bob Phillips]

Can you give some data details and your actual formula.

--

HTH

Bob Phillips

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

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

"Bob Phillips" <[email protected]> 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)

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.

 

[Roger Govier]

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 A>Data>Filter>Advanced Filter>Copy to another location>
Sheet2!A1>Unique 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 Data>Validation>List> =Mynames
Then use the dropdown on D2 to select the Client and you will get the
required result.

--
Regards

Roger Govier


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.

 

[Guest]

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

 

[Guest]

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)

 

[Arvi Laanemets]

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.

 

[Roger Govier]

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

 

[Arvi Laanemets]

Hi

Hi Arvi

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

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

Here I moved the placement of formulas at top of page

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

 

[Roger Govier]

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

 

[Arvi Laanemets]

Hi

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

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]

Hi



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


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.