Partial / Conditional Sum of a Column

[Guest]

There are 12 figures in a column (A1:A12). B1 is a parameter Cell. when I
enter 3 in the parameter cell (B1), result must be {sum(A1:A3), sum(A4:A6),
sum (A
7:A9), sum(a10:a11)}. I Mean to say sum function must be handled with the
paramenter cell (B1), and result will be in other column with parameter
interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc.

 

[Biff]

Try this:

=IF(B$1="","",IF(ROWS($1:1)<=CEILING(COUNT(A$1:A$12)/B$1,1),SUM(OFFSET(A$1,(ROWS($1:1)-1)*B$1,,B$1)),""))

result will be in other column with parameter
interval i.e., value of (B1) i.e., 3 or 6 or 9 or 1 etc.

If you use an interval of 1 then you have to copy the formula down to a
number of cells that's equal to the size of your range.

Biff

"Mujeeb ur Rehman, FBL-GRW-PK"

 

[Biff]

Caveat:

I'm assuming that you have all cells filled:

There are 12 figures in a column

Otherwise, the formula I suggested won't work as expected if there may be
empty/blanks cells within the range.

Biff

 

[Guest]

Biff, thank u very much for your co-operation. I have applied your given
formula and got sufficient result but not 100%. Perhaps I could not explain
my problem, well think about the following, which I want

A B C
Input para Result
1 1 3 -
2 2 -
3 3 6
4 4 -
5 5 -
6 6 15
7 7 -
8 8 -
9 9 24
10 10 -
11 11 -
12 12 33

Column A is a base column, B is parameter Column and C is Result Column. Now
suppose I enter 6 in B1, the result must be (C1,C2,C3,C4,C5)=0 & C6=21
similarly (C7,C8,C9,C10,C11)=0 & C12 = 57. This is actually handling sum
function with parameter.

If you have any solution in this regard, please guide me.

Hoping for best.

Thanks again for contact.

Mujeeb ur Rehman

 

[Guest]

If B1 is a parameter cell, B2:B12 is blank ?

Assume only B1 contain the parameter...B2:B12 is blank...

Column A values may vary ? or a fixed increasing series numbers from 1 to n?

Parameter B1 is a number of step for each cells with values, is it?

Coz looking on sample may not guarantee the question....

 

[Guest]

Maybe this one...
on cell
C1 : =IF($B$1=1,A1,0)
on cells C2:C12 :
=
IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0))
hope this help...

 

[Guest]

Try this:

Assuming your data start from A1
In B1 =IF(MOD(A1,3)=0,SUM(INDIRECT("A"&1*ROW()-2):OFFSET(A1,0,0)),"")

 

[Biff]

I guess this does what the OP wants but as posted it has a circular
reference and the references are incorrect:

C1 : =IF($B$1=1,A1,0)

Change to:

=IF($B$1=1,A1,"")

on cells C2:C12 :

=IF($B$1=1,A12,IF(ROW(A12)/$B$1=ROUND(ROW(A12)/$B$1,0),SUM($A$1:A12)-SUM($C$1:C11),0))

Change to:

=IF($B$1=1,A2,IF(ROW(A2)/B$1=ROUND(ROW(A2)/B$1,0),SUM(A$1:A2)-SUM(C$1:C1),""))

Biff

 

[Guest]

Hi biff....looks quick changing my formula 0 (zero) to "" (blank), but Majeeb
has a "-"....what do tou think, can a blank or a space be shown in excel ?
just amazed...about the changing result...

 

[Biff]

can a blank or a space be shown in excel ?

Not sure what you mean?

If the OP wants a dash: "-", just replace the "" with "-".

Biff

 

[Guest]

nice catch for a long formula...

Not sure what you mean?

If the OP wants a dash: "-", just replace the "" with "-".

Biff

 

[Guest]

from your post reading it as "the result must be (C1,C2,C3,C4,C5)=0 & C6=21"
Meaning no blank or minus character ......."" or "-" as sub-results....

Hope this fits clearly your request and without confusing ideas...
from
C2
=IF($B$1=1,A2,IF(ROW(A2)/$B$1=ROUND(ROW(A2)/$B$1,0),SUM($A$1:A2)-SUM($C$1:C1),0))
copy paste down to C12
in C1 =IF($B$1=1,A1,0)
....