subtotal(9,....
 

Hi all,

I have an autofiltered page that looks like this:

Title Name/Role Project Identification June
July Aug
Supply Fred Astaire Project Name 1 20%
30%
Supply Joe Bloggs Project Name 2 30%
30%
Demand Project Manager Project Name 1 40%
etc
Demand Project Manager Project Name 2 50%
Demand Project Manager Project Name 3 60%
etc.
etc.

I want to be able to put a subtotal on the % columns that will allow me
to effectively view the supply versus demand for the filters that I
select.

I currently have subtotal(9, calcs that do show me the total of the %
columns as a total (whole) number but it's not broken up by Supply and
Demand which is what I need.

If there a way to wrap the subtotal(9 command into something else to
allow me to do this?

Yours hopefully.

Shane - Sydney, AUS.


--
Shane Moore
------------------------------------------------------------------------
Shane Moore's Profile: http://www.excelforum.com/member.php...o&userid=36421
View this thread: http://www.excelforum.com/showthread...hreadid=561936

subtotal(9,....
 

Shane,

If you select all columns and then do DataSubtotals, change on Title, and
click the June, July, Aug, etc boxes to add subtotals to, then you should
automatically get subtotals at each break.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Shane Moore"
wrote in message
...

Hi all,

I have an autofiltered page that looks like this:

Title Name/Role Project Identification June
July Aug
Supply Fred Astaire Project Name 1 20%
30%
Supply Joe Bloggs Project Name 2 30%
30%
Demand Project Manager Project Name 1 40%
etc
Demand Project Manager Project Name 2 50%
Demand Project Manager Project Name 3 60%
etc.
etc.

I want to be able to put a subtotal on the % columns that will allow me
to effectively view the supply versus demand for the filters that I
select.

I currently have subtotal(9, calcs that do show me the total of the %
columns as a total (whole) number but it's not broken up by Supply and
Demand which is what I need.

If there a way to wrap the subtotal(9 command into something else to
allow me to do this?

Yours hopefully.

Shane - Sydney, AUS.


--
Shane Moore
------------------------------------------------------------------------
Shane Moore's Profile:
http://www.excelforum.com/member.php...o&userid=36421
View this thread: http://www.excelforum.com/showthread...hreadid=561936

subtotal(9,....
 

Hi.

Thanks for the response.

I used the following which works nicely.

=SUM(($A4:A$80="Demand")*(SUBTOTAL(9,OFFSET(F4:F80 ,ROW(F4:F80)-MIN(ROW(F4:F80)),,1))))


--
Shane Moore
------------------------------------------------------------------------
Shane Moore's Profile: http://www.excelforum.com/member.php...o&userid=36421
View this thread: http://www.excelforum.com/showthread...hreadid=561936