Prev: Simplify Formula
Next: drop info auto from sheet 1 to 2
From: Bruce on 3 Jul 2008 21:06
I'm trying to make a commission schedule with basic setup like as follows:

A B C D E
1 Date Client Type Pay Tax
2 12 Jun Smith A 100 10
3 19 Jun Jones B 240 24
4 21 Jun Frost A 180 18
5 23 Jul Kris C 500 50

etc etc

Just say I wanted to put a 'Date' filter and a 'Type' filter on such as June
clients who are type A. This would reveal Rows 2 and 4.

I want a cell that I can label 'Total Pay' that will only show the sum of
visible cells which would be Rows 2 & 4 and would therefore show 280

Currently I am using =sum(D2:D5) which includes all cells whether they are
visible or not. I read the 'ignoring visible cells' article but that didn't
work.

Can someone tell me how to get a some of the filtered cells ONLY?
From: bpeltzer on 3 Jul 2008 21:19
=subtotal(9,D2:D5)
If you have an active filter and use the Autosum button, you'll get that
subtotal function where you'd normally get a sum function.

"Bruce" wrote:

> I'm trying to make a commission schedule with basic setup like as follows:
>
> A B C D E
> 1 Date Client Type Pay Tax
> 2 12 Jun Smith A 100 10
> 3 19 Jun Jones B 240 24
> 4 21 Jun Frost A 180 18
> 5 23 Jul Kris C 500 50
>
> etc etc
>
> Just say I wanted to put a 'Date' filter and a 'Type' filter on such as June
> clients who are type A. This would reveal Rows 2 and 4.
>
> I want a cell that I can label 'Total Pay' that will only show the sum of
> visible cells which would be Rows 2 & 4 and would therefore show 280
>
> Currently I am using =sum(D2:D5) which includes all cells whether they are
> visible or not. I read the 'ignoring visible cells' article but that didn't
> work.
>
> Can someone tell me how to get a some of the filtered cells ONLY?
From: Bruce on 3 Jul 2008 22:11
Thanks bpeltzer you're a lifesaver!

"bpeltzer" wrote:

> =subtotal(9,D2:D5)
> If you have an active filter and use the Autosum button, you'll get that
> subtotal function where you'd normally get a sum function.
>
> "Bruce" wrote:
>
> > I'm trying to make a commission schedule with basic setup like as follows:
> >
> > A B C D E
> > 1 Date Client Type Pay Tax
> > 2 12 Jun Smith A 100 10
> > 3 19 Jun Jones B 240 24
> > 4 21 Jun Frost A 180 18
> > 5 23 Jul Kris C 500 50
> >
> > etc etc
> >
> > Just say I wanted to put a 'Date' filter and a 'Type' filter on such as June
> > clients who are type A. This would reveal Rows 2 and 4.
> >
> > I want a cell that I can label 'Total Pay' that will only show the sum of
> > visible cells which would be Rows 2 & 4 and would therefore show 280
> >
> > Currently I am using =sum(D2:D5) which includes all cells whether they are
> > visible or not. I read the 'ignoring visible cells' article but that didn't
> > work.
> >
> > Can someone tell me how to get a some of the filtered cells ONLY?
 | 
Pages: 1
Prev: Simplify Formula
Next: drop info auto from sheet 1 to 2