countif/offset problem

S

Scot B

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.
 
R

Ron Coderre

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
 
P

Peo Sjoblom

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

duane

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

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!
 
H

Harlan Grove

duane said:
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))
Click to expand...
....

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