Need a simple subtotal Formula
 

Hi

Let's say i have a column like this:
A __ B
1
1
1
1__formula says "4"
empty
empty
empty
1
1
1
1
1__formula says "5"


Is there a formula I can place in B to subtotal what is in A? All I
want to do is get the totals for each little group on the way down. I
am trying to get around the Subtotals ... menu command in the Data
Menu

Thanks

tx


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=505199

Need a simple subtotal Formula
 

Is =SUMIF(A:A,1) any good?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"tx12345" wrote in
message ...

Hi

Let's say i have a column like this:
A __ B
1
1
1
1__formula says "4"
empty
empty
empty
1
1
1
1
1__formula says "5"


Is there a formula I can place in B to subtotal what is in A? All I
want to do is get the totals for each little group on the way down. I
am trying to get around the Subtotals ... menu command in the Data
Menu

Thanks

tx


--
tx12345
------------------------------------------------------------------------
tx12345's Profile:
http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=505199

Need a simple subtotal Formula
 

Hi Bob, Thanks for the idea, but =sumif(A:A,1) totals every instance
of 1 for the entire column and returns the result.

Here is the hack I came up with:

in W11 and on down as needed:
=IF(AND(S10=0,S11=1,S12=1),ROW(),IF(AND(S10=1,S11= 1,S12=0),ROW()+1,""))

The "1s" occur in clusters marking the beginning and end of either a
buy cycle OHLC up and above the 21d moving average, or a sell cycle
below it. But I need to know on the fly how many cycles there have
been, and what the duration of each cycle was so I can be more precise
about when I execute a trade.

The next thing I did was place in W9:
=COUNT(W10:W274)

This gives me the total number of occurances of where a row number pops
up in the column. Since there will always be two for each beginning and
end of a cycle, dividing that number by two tells me how many cycles
there have been for the issue under study, and for the time frame
covered.

Since the row numbers progressively increase and are always unique, I
then use the LARGE function to produce my subtotals. With this hack I
accidentally did something convenient, as all the subtotals are neatly
grouped at the top of the page w/o having to sort.

In Y10 on down to Y41 (there are typically not more than 21 cycles for
the time fame I am working with):

=IF(W9=2,SUM(LARGE(W11:W275,W9-1)-LARGE(W11:W275,W9)),"")
=IF(W9=4,SUM(LARGE(W11:W275,W9-3)-LARGE(W11:W275,W9-2)),"")

etc, working my way down, using the prioritizing feature of the LARGE
function to subtract the larger ROW number from the lesser one in the
pair of the start and finish of that particular cycle.

Its a clumsy solution, but it was all I could come up with.

Tx


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=505199