countif/offset problem

Greetings, Experts,

I'm sorry for not following the earlier advice, i just don't get it.
perhaps i'm not explaining well.

We're trying to define the formula that will look at a row of data and sum a
specific number of cells into a new cell (called the new cell A2). then,
cell B2 picks up summing the row for a specified number of cells and puts
that sum into B2.

Here's the sample data, in a row. Row 1: 10,35,50,7,25,48

Cell A7 has the value "3", which means that we'd like to put the sum of the
first 3 numbers in Row1 into cell A2.

Cell A8 has the value "2", which means we'd like to look at the row of data
and pick up where the first formula left off (the fourth position in the
row, cell A4), and sum the value in the 2 cells.

So, in this simple example, cell A2 would result in a value of 95
(10+35+50), and cell B2 would result in a value of 32 (7+25)

Then, cell C2 would pick up where the previous formula left off and go for a
certain number of cells....

Thanks for your help!!!! this has been a sticking point for hours.

Cheers,

Scot B.

Try this:

First, for better utility, I put your offset params in cells....
A3: 3
B3: 2
C3: 3
etc

and the summations in A2, B2, C2, etc
A2: =SUM(OFFSET($A1,0,0,1,A3))
B2: =SUM(OFFSET($A1,0,SUM($A3:A3),1,B3))
Copy that formula across row 2 as far as necessary.

Does that help?

***********
Regards,
Ron


"Scot B" wrote:

> Greetings, Experts,
>
> I'm sorry for not following the earlier advice, i just don't get it.
> perhaps i'm not explaining well.
>
> We're trying to define the formula that will look at a row of data and sum a
> specific number of cells into a new cell (called the new cell A2). then,
> cell B2 picks up summing the row for a specified number of cells and puts
> that sum into B2.
>
> Here's the sample data, in a row. Row 1: 10,35,50,7,25,48
>
> Cell A7 has the value "3", which means that we'd like to put the sum of the
> first 3 numbers in Row1 into cell A2.
>
> Cell A8 has the value "2", which means we'd like to look at the row of data
> and pick up where the first formula left off (the fourth position in the
> row, cell A4), and sum the value in the 2 cells.
>
> So, in this simple example, cell A2 would result in a value of 95
> (10+35+50), and cell B2 would result in a value of 32 (7+25)
>
> Then, cell C2 would pick up where the previous formula left off and go for a
> certain number of cells....
>
> Thanks for your help!!!! this has been a sticking point for hours.
>
> Cheers,
>
> Scot B.
>
>
>

that worked perfectly...i don't understand it, but at this hour, just
getting it working is a victory.

thanks so much for your help!

"Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:33F9F619-EC40-4D95-9393-34F61C305711@microsoft.com...
> Try this:
>
> First, for better utility, I put your offset params in cells....
> A3: 3
> B3: 2
> C3: 3
> etc
>
> and the summations in A2, B2, C2, etc
> A2: =SUM(OFFSET($A1,0,0,1,A3))
> B2: =SUM(OFFSET($A1,0,SUM($A3:A3),1,B3))
> Copy that formula across row 2 as far as necessary.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
>
> "Scot B" wrote:
>
>> Greetings, Experts,
>>
>> I'm sorry for not following the earlier advice, i just don't get it.
>> perhaps i'm not explaining well.
>>
>> We're trying to define the formula that will look at a row of data and
>> sum a
>> specific number of cells into a new cell (called the new cell A2). then,
>> cell B2 picks up summing the row for a specified number of cells and puts
>> that sum into B2.
>>
>> Here's the sample data, in a row. Row 1: 10,35,50,7,25,48
>>
>> Cell A7 has the value "3", which means that we'd like to put the sum of
>> the
>> first 3 numbers in Row1 into cell A2.
>>
>> Cell A8 has the value "2", which means we'd like to look at the row of
>> data
>> and pick up where the first formula left off (the fourth position in the
>> row, cell A4), and sum the value in the 2 cells.
>>
>> So, in this simple example, cell A2 would result in a value of 95
>> (10+35+50), and cell B2 would result in a value of 32 (7+25)
>>
>> Then, cell C2 would pick up where the previous formula left off and go
>> for a
>> certain number of cells....
>>
>> Thanks for your help!!!! this has been a sticking point for hours.
>>
>> Cheers,
>>
>> Scot B.
>>
>>
>>

1.

=SUM(INDEX(1:1,,1):INDEX(1:1,,A7))

or

=SUM(OFFSET($A$1,,,,A7))

2.

=SUM(INDEX(1:1,,A7+1):INDEX(1:1,,A7+A8))

or

=SUM(OFFSET($A$1,,A7,,A8))


I prefer the INDEX since it is non volatile

--
Regards,

Peo Sjoblom

(No private emails please)


"Scot B" <news@chicagomedia.com> wrote in message
news:3Z6dnaXV4K6tlwTenZ2dnUVZ_sadnZ2d@comcast.com...
> Greetings, Experts,
>
> I'm sorry for not following the earlier advice, i just don't get it.
> perhaps i'm not explaining well.
>
> We're trying to define the formula that will look at a row of data and sum
> a specific number of cells into a new cell (called the new cell A2).
> then, cell B2 picks up summing the row for a specified number of cells and
> puts that sum into B2.
>
> Here's the sample data, in a row. Row 1: 10,35,50,7,25,48
>
> Cell A7 has the value "3", which means that we'd like to put the sum of
> the first 3 numbers in Row1 into cell A2.
>
> Cell A8 has the value "2", which means we'd like to look at the row of
> data and pick up where the first formula left off (the fourth position in
> the row, cell A4), and sum the value in the 2 cells.
>
> So, in this simple example, cell A2 would result in a value of 95
> (10+35+50), and cell B2 would result in a value of 32 (7+25)
>
> Then, cell C2 would pick up where the previous formula left off and go for
> a certain number of cells....
>
> Thanks for your help!!!! this has been a sticking point for hours.
>
> Cheers,
>
> Scot B.
>

your row of data starting in cell a1

your numbers in cells a7 and a8 (and down if you like)

this in cell a2 and copy to b2, c2 etc

=SUM(OFFSET($A$1,0,SUM($A$6:INDIRECT("A"&COLUMN()+5)),1,1):OFFSET($A$1,0,SUM($A$6:INDIRECT("A"&COLUMN()+6))-1,1,1))

"Scot B" wrote:

> Greetings, Experts,
>
> I'm sorry for not following the earlier advice, i just don't get it.
> perhaps i'm not explaining well.
>
> We're trying to define the formula that will look at a row of data and sum a
> specific number of cells into a new cell (called the new cell A2). then,
> cell B2 picks up summing the row for a specified number of cells and puts
> that sum into B2.
>
> Here's the sample data, in a row. Row 1: 10,35,50,7,25,48
>
> Cell A7 has the value "3", which means that we'd like to put the sum of the
> first 3 numbers in Row1 into cell A2.
>
> Cell A8 has the value "2", which means we'd like to look at the row of data
> and pick up where the first formula left off (the fourth position in the
> row, cell A4), and sum the value in the 2 cells.
>
> So, in this simple example, cell A2 would result in a value of 95
> (10+35+50), and cell B2 would result in a value of 32 (7+25)
>
> Then, cell C2 would pick up where the previous formula left off and go for a
> certain number of cells....
>
> Thanks for your help!!!! this has been a sticking point for hours.
>
> Cheers,
>
> Scot B.
>
>
>

"duane" <duane@discussions.microsoft.com> wrote...
>your row of data starting in cell a1
>
>your numbers in cells a7 and a8 (and down if you like)
>
>this in cell a2 and copy to b2, c2 etc
>
>=SUM(OFFSET($A$1,0,SUM($A$6:INDIRECT("A"&COLUMN()+5)),1,1)
>:OFFSET($A$1,0,SUM($A$6:INDIRECT("A"&COLUMN()+6))-1,1,1))
....

OFFSET():OFFSET() is unnecessary. If both OFFSET calls resolve to single
cells, then OFFSET(a,b,c):OFFSET(x,y,z) is always equivalent to

OFFSET(a,b,c,MIN(ROW(x))-MIN(ROW(a))+y-b+1,
MIN(COLUMN(x))-MIN(COLUMN(a))+z-c+1)

When a = x, it reduces further to

OFFSET(a,b,c,y-b+1,z-c+1)

So your formula could be reduced to

A2:
=SUM(OFFSET($A$1,0,SUM(OFFSET($A$6,0,0,COLUMNS($A2:A2),1)),1,
OFFSET($A$6,COLUMNS($A2:A2),0)))

But even better would be using only nonvolatile functions.

A2:
=SUM(INDEX($1:$1,SUM(1,$A$6:INDEX($A$6:$A$65536,COLUMNS($A2:A2))))
:INDEX($1:$1,SUM($A$6:INDEX($A$6:$A$65536,COLUMNS($A2:B2)))))