subtotal(9,....

[Shane Moore]

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.

[Bob Phillips]

Shane,

If you select all columns and then do Data>Subtotals, 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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Shane Moore]

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