I have a spreadsheet which will build up over the next few weeks with each
weeks results adding four columns of data to the sheet €“ sheet1.

I then want to perform analysis on the data in another sheet €“ sheet2

eg. In cell A1: SUM(sheet1!B8,sheet1!F8,sheet1!J8,etc,
and in cell B1: COUNT(sheet1!B8,sheet1!F8,sheet1!J8,etc,
etc

Is there a way of calculating €œevery 4th cell€� in a row. There will be
several weeks of data and doing this long-hand (as above) is time consuming.
I dont yet know how many weeks of data there will be and I dont want to
have to add extra terms to the COUNT, SUM, etc each time I add a new week.

Perhaps there is a way of creating a Range of this nature€¦? I clutch at
straws. Can you help?

Try experimenting with these:
The sum
A1: =SUMPRODUCT((MOD(COLUMN(Sheet1!8:8)-2,4)=0)*(Sheet1!8:8))

The count
B1: =SUMPRODUCT((MOD(COLUMN(Sheet1!8:8)-2,4)=0)*ISNUMBER(Sheet1!8:8))

Does that help?

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

XL2002, WinXP-Pro


"Hugh Murfitt" wrote:

I have a spreadsheet which will build up over the next few weeks with each
weeks results adding four columns of data to the sheet €“ sheet1.

I then want to perform analysis on the data in another sheet €“ sheet2

eg. In cell A1: SUM(sheet1!B8,sheet1!F8,sheet1!J8,etc,
and in cell B1: COUNT(sheet1!B8,sheet1!F8,sheet1!J8,etc,
etc

Is there a way of calculating €œevery 4th cell€� in a row. There will be
several weeks of data and doing this long-hand (as above) is time consuming.
I dont yet know how many weeks of data there will be and I dont want to
have to add extra terms to the COUNT, SUM, etc each time I add a new week.

Perhaps there is a way of creating a Range of this nature€¦? I clutch at
straws. Can you help?

You can achieve this by using MOD()...

In A1: =SUMPRODUCT(--(MOD(COLUMN(A8:IV8),4)=2),A8:IV8)
In B1: =SUMPRODUCT(--(MOD(COLUMN(A8:IV8),4)=2),--(A8:IV8<""))

Does that help?
--
Regards,
Dave


"Hugh Murfitt" wrote:

I have a spreadsheet which will build up over the next few weeks with each
weeks results adding four columns of data to the sheet €“ sheet1.

I then want to perform analysis on the data in another sheet €“ sheet2

eg. In cell A1: SUM(sheet1!B8,sheet1!F8,sheet1!J8,etc,
and in cell B1: COUNT(sheet1!B8,sheet1!F8,sheet1!J8,etc,
etc

Is there a way of calculating €œevery 4th cell€� in a row. There will be
several weeks of data and doing this long-hand (as above) is time consuming.
I dont yet know how many weeks of data there will be and I dont want to
have to add extra terms to the COUNT, SUM, etc each time I add a new week.

Perhaps there is a way of creating a Range of this nature€¦? I clutch at
straws. Can you help?

If you had a heading above €œevery 4th cell€� souch as Total what you could
use is a SUMIF function i.e. =SUMIF(Range(i.e the row where the heading is),
"Total", sum range ( the range with the values you want to sum))
That should work

"Hugh Murfitt" wrote:

I have a spreadsheet which will build up over the next few weeks with each
weeks results adding four columns of data to the sheet €“ sheet1.

I then want to perform analysis on the data in another sheet €“ sheet2

eg. In cell A1: SUM(sheet1!B8,sheet1!F8,sheet1!J8,etc,
and in cell B1: COUNT(sheet1!B8,sheet1!F8,sheet1!J8,etc,
etc

Is there a way of calculating €œevery 4th cell€� in a row. There will be
several weeks of data and doing this long-hand (as above) is time consuming.
I dont yet know how many weeks of data there will be and I dont want to
have to add extra terms to the COUNT, SUM, etc each time I add a new week.

Perhaps there is a way of creating a Range of this nature€¦? I clutch at
straws. Can you help?

Thanks, guys, but I can't get this to work.

I dont really understand whats happening so I cant experiment very
effectively. I think I understand what each individual operator/function
does independently but not in the combination youve used them. What is
COLUMN(Sheet1!8:8) doing here?

Incidentally, if you have time to explain, why did Ron use €œsheet!€� where
David didnt? And what does €œ - - €œ do?

"Ron Coderre" wrote:

Try experimenting with these:
The sum
A1: =SUMPRODUCT((MOD(COLUMN(Sheet1!8:8)-2,4)=0)*(Sheet1!8:8))

The count
B1: =SUMPRODUCT((MOD(COLUMN(Sheet1!8:8)-2,4)=0)*ISNUMBER(Sheet1!8:8))

Does that help?

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

XL2002, WinXP-Pro


"Hugh Murfitt" wrote:

I have a spreadsheet which will build up over the next few weeks with each
weeks results adding four columns of data to the sheet €“ sheet1.

I then want to perform analysis on the data in another sheet €“ sheet2

eg. In cell A1: SUM(sheet1!B8,sheet1!F8,sheet1!J8,etc,
and in cell B1: COUNT(sheet1!B8,sheet1!F8,sheet1!J8,etc,
etc

Is there a way of calculating €œevery 4th cell€� in a row. There will be
several weeks of data and doing this long-hand (as above) is time consuming.
I dont yet know how many weeks of data there will be and I dont want to
have to add extra terms to the COUNT, SUM, etc each time I add a new week.

Perhaps there is a way of creating a Range of this nature€¦? I clutch at
straws. Can you help?

Thanks,
I do have a heading as you describe in row 7. And the data is in starts in
B8 and will grow right and down. If I use =SUMIF(Sheet1!7:7,"Total",
Sheet1!B8:Z40) how do I fill down?

"dannykuk" wrote:

If you had a heading above €œevery 4th cell€� souch as Total what you could
use is a SUMIF function i.e. =SUMIF(Range(i.e the row where the heading is),
"Total", sum range ( the range with the values you want to sum))
That should work

"Hugh Murfitt" wrote:

I have a spreadsheet which will build up over the next few weeks with each
weeks results adding four columns of data to the sheet €“ sheet1.

I then want to perform analysis on the data in another sheet €“ sheet2

eg. In cell A1: SUM(sheet1!B8,sheet1!F8,sheet1!J8,etc,
and in cell B1: COUNT(sheet1!B8,sheet1!F8,sheet1!J8,etc,
etc

Is there a way of calculating €œevery 4th cell€� in a row. There will be
several weeks of data and doing this long-hand (as above) is time consuming.
I dont yet know how many weeks of data there will be and I dont want to
have to add extra terms to the COUNT, SUM, etc each time I add a new week.

Perhaps there is a way of creating a Range of this nature€¦? I clutch at
straws. Can you help?

OK...
I'll start with this: The Sheet1! reference.
I didn't know if your calculations were on a sheet other than Sheet1, so I
included the reference to that sheet.

COLUMN(Sheet1!8:8)
The COLUMN function returns the column number of the reference. Col_A is 1,
Col_B is 2, etc. When the MOD of the column number divided by 4 i= 0, that
column is a multiple of 4. Example: columns D and H (4 and 8) are divisible
by 4 with no remainder.

Since you wanted to include every 4th cell beginning with and including B8,
I had to adjust the column number reference by subtracting 2. Consequently, A
is -1, B is 0, C is 1, etc.

The -- operator
When a boolean expression is used (eg A1=B1) it returns TRUE or FALSE. We
need those values to be numbers so we cause an implicit type conversion from
Boolean to Numeric by making the value negative...then positive.
-TRUE=-1...--TRUE=+1.


If you have more questions..just post them.

Does that help?

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

XL2002, WinXP-Pro


"Hugh Murfitt" wrote:

Thanks, guys, but I can't get this to work.

I dont really understand whats happening so I cant experiment very
effectively. I think I understand what each individual operator/function
does independently but not in the combination youve used them. What is
COLUMN(Sheet1!8:8) doing here?

Incidentally, if you have time to explain, why did Ron use €œsheet!€� where
David didnt? And what does €œ - - €œ do?

"Ron Coderre" wrote:

Try experimenting with these:
The sum
A1: =SUMPRODUCT((MOD(COLUMN(Sheet1!8:8)-2,4)=0)*(Sheet1!8:8))

The count
B1: =SUMPRODUCT((MOD(COLUMN(Sheet1!8:8)-2,4)=0)*ISNUMBER(Sheet1!8:8))

Does that help?

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

XL2002, WinXP-Pro


"Hugh Murfitt" wrote:

I have a spreadsheet which will build up over the next few weeks with each
weeks results adding four columns of data to the sheet €“ sheet1.

I then want to perform analysis on the data in another sheet €“ sheet2

eg. In cell A1: SUM(sheet1!B8,sheet1!F8,sheet1!J8,etc,
and in cell B1: COUNT(sheet1!B8,sheet1!F8,sheet1!J8,etc,
etc

Is there a way of calculating €œevery 4th cell€� in a row. There will be
several weeks of data and doing this long-hand (as above) is time consuming.
I dont yet know how many weeks of data there will be and I dont want to
have to add extra terms to the COUNT, SUM, etc each time I add a new week.

Perhaps there is a way of creating a Range of this nature€¦? I clutch at
straws. Can you help?

A1: =SUMPRODUCT(--(MOD(COLUMN(8:8),4)=2),8:8)
B1: =SUMPRODUCT(--(MOD(COLUMN(Sheet1!8:8),4)=2),--(ISNUMBER(Sheet1!8:8)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hugh Murfitt" wrote in message
...
I have a spreadsheet which will build up over the next few weeks with each
week's results adding four columns of data to the sheet - sheet1.

I then want to perform analysis on the data in another sheet - sheet2

eg. In cell A1: SUM(sheet1!B8,sheet1!F8,sheet1!J8,etc,
and in cell B1: COUNT(sheet1!B8,sheet1!F8,sheet1!J8,etc,
etc

Is there a way of calculating "every 4th cell" in a row. There will be
several weeks of data and doing this long-hand (as above) is time
consuming.
I don't yet know how many weeks of data there will be and I don't want to
have to add extra terms to the COUNT, SUM, etc each time I add a new week.

Perhaps there is a way of creating a Range of this nature.? I clutch at
straws. Can you help?