Hi

Still can't get this formula to work. It is referencing the column no and
bring the data in that column but is not adding the colums up to that column
no. Ir if if C8 = 9 and the value in col 9 is 4211.00, then the formula is
showing 4211 and not the sum of all the columns up to column 9.

Hi
now that helps :-)
try the following formula:
=SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('turnov er'!$A$1:$A$1000,0,MATCH($C8,'turnover'!$B$1:$N$1 ,0)))

--
Tx
Jules

Hi,

What was your original posting ? Can you post that as well.

Or else stick to your earlier thread to post any comments.

Regards

Govind.

Jules wrote:
Hi

Still can't get this formula to work. It is referencing the column no and
bring the data in that column but is not adding the colums up to that column
no. Ir if if C8 = 9 and the value in col 9 is 4211.00, then the formula is
showing 4211 and not the sum of all the columns up to column 9.


Hi
now that helps :-)
try the following formula:
=SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('turno ver'!$A$1:$A$1000,0,MATCH($C8,'turnover'!$B$1:$N$ 1,0)))

Apologies thought that as the posting was on the 29/10/04 it was too old for
replies. The original posting was:
I have a spreadsheet set up which feeds in monthly figures eg in jan figures
are put in under the jan heading. in feb figures are put in under the feb
heading etc. at the bottom of the sheet is a total for all months, but need
to write a formula to add only two columns if the month is feb or 10 columns
if month is october. The Supplier Name is in col B, the month number is in C8
of sheet 'Summary' and the data it needs to add is in the sheet 'turnover'
with the Supplier name in col A and the months starting with Jan in the
following colums ie Jan in B, Feb in C etc with the turnover per month in the
relative rows for the Supplier. What I want the formula to do is to lookup a
Supplier name in Turnover (col A) that is in the Summary Sheet (col B), read
the no of periods (col C in Summary) and then sum that no of columns in the
Turnover sheet pertaining to the Supplier.
Tx

"Govind" wrote:

Hi,

What was your original posting ? Can you post that as well.

Or else stick to your earlier thread to post any comments.

Regards

Govind.

Jules wrote:
Hi

Still can't get this formula to work. It is referencing the column no and
bring the data in that column but is not adding the colums up to that column
no. Ir if if C8 = 9 and the value in col 9 is 4211.00, then the formula is
showing 4211 and not the sum of all the columns up to column 9.


Hi
now that helps :-)
try the following formula:
=SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('turno ver'!$A$1:$A$1000,0,MATCH($C8,'turnover'!$B$1:$N$ 1,0)))

Hi Jules,

Try

=SUM(OFFSET(INDIRECT("turnover!"&ADDRESS(MATCH(B8, turnover!A1:A1000,0),1)),0,1,1,MATCH(Sheet1!C8,tur nover!B1:N1,0)))

Regards

Govind


Jules wrote:

Apologies thought that as the posting was on the 29/10/04 it was too old for
replies. The original posting was:
I have a spreadsheet set up which feeds in monthly figures eg in jan figures
are put in under the jan heading. in feb figures are put in under the feb
heading etc. at the bottom of the sheet is a total for all months, but need
to write a formula to add only two columns if the month is feb or 10 columns
if month is october. The Supplier Name is in col B, the month number is in C8
of sheet 'Summary' and the data it needs to add is in the sheet 'turnover'
with the Supplier name in col A and the months starting with Jan in the
following colums ie Jan in B, Feb in C etc with the turnover per month in the
relative rows for the Supplier. What I want the formula to do is to lookup a
Supplier name in Turnover (col A) that is in the Summary Sheet (col B), read
the no of periods (col C in Summary) and then sum that no of columns in the
Turnover sheet pertaining to the Supplier.
Tx

"Govind" wrote:


Hi,

What was your original posting ? Can you post that as well.

Or else stick to your earlier thread to post any comments.

Regards

Govind.

Jules wrote:

Hi

Still can't get this formula to work. It is referencing the column no and
bring the data in that column but is not adding the colums up to that column
no. Ir if if C8 = 9 and the value in col 9 is 4211.00, then the formula is
showing 4211 and not the sum of all the columns up to column 9.



Hi
now that helps :-)
try the following formula:
=SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('tur nover'!$A$1:$A$1000,0,MATCH($C8,'turnover'!$B$1:$ N$1,0)))

Getting #N/A result! Bit confused as should it not be reading the whole of
the Turnover sheet ie B2:Z600?

Tx


"Govind" wrote:

Hi Jules,

Try

=SUM(OFFSET(INDIRECT("turnover!"&ADDRESS(MATCH(B8, turnover!A1:A1000,0),1)),0,1,1,MATCH(Sheet1!C8,tur nover!B1:N1,0)))

Regards

Govind


Jules wrote:

Apologies thought that as the posting was on the 29/10/04 it was too old for
replies. The original posting was:
I have a spreadsheet set up which feeds in monthly figures eg in jan figures
are put in under the jan heading. in feb figures are put in under the feb
heading etc. at the bottom of the sheet is a total for all months, but need
to write a formula to add only two columns if the month is feb or 10 columns
if month is october. The Supplier Name is in col B, the month number is in C8
of sheet 'Summary' and the data it needs to add is in the sheet 'turnover'
with the Supplier name in col A and the months starting with Jan in the
following colums ie Jan in B, Feb in C etc with the turnover per month in the
relative rows for the Supplier. What I want the formula to do is to lookup a
Supplier name in Turnover (col A) that is in the Summary Sheet (col B), read
the no of periods (col C in Summary) and then sum that no of columns in the
Turnover sheet pertaining to the Supplier.
Tx

"Govind" wrote:


Hi,

What was your original posting ? Can you post that as well.

Or else stick to your earlier thread to post any comments.

Regards

Govind.

Jules wrote:

Hi

Still can't get this formula to work. It is referencing the column no and
bring the data in that column but is not adding the colums up to that column
no. Ir if if C8 = 9 and the value in col 9 is 4211.00, then the formula is
showing 4211 and not the sum of all the columns up to column 9.



Hi
now that helps :-)
try the following formula:
=SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('tur nover'!$A$1:$A$1000,0,MATCH($C8,'turnover'!$B$1:$ N$1,0)))

Apologies once again - you were refering to the range in the "turnover" and I
have now got the formula to work and it does exactly what I want - Thank You
'this has been driving me nuts!!!

Tx


"Govind" wrote:

Hi Jules,

Try

=SUM(OFFSET(INDIRECT("turnover!"&ADDRESS(MATCH(B8, turnover!A1:A1000,0),1)),0,1,1,MATCH(Sheet1!C8,tur nover!B1:N1,0)))

Regards

Govind


Jules wrote:

Apologies thought that as the posting was on the 29/10/04 it was too old for
replies. The original posting was:
I have a spreadsheet set up which feeds in monthly figures eg in jan figures
are put in under the jan heading. in feb figures are put in under the feb
heading etc. at the bottom of the sheet is a total for all months, but need
to write a formula to add only two columns if the month is feb or 10 columns
if month is october. The Supplier Name is in col B, the month number is in C8
of sheet 'Summary' and the data it needs to add is in the sheet 'turnover'
with the Supplier name in col A and the months starting with Jan in the
following colums ie Jan in B, Feb in C etc with the turnover per month in the
relative rows for the Supplier. What I want the formula to do is to lookup a
Supplier name in Turnover (col A) that is in the Summary Sheet (col B), read
the no of periods (col C in Summary) and then sum that no of columns in the
Turnover sheet pertaining to the Supplier.
Tx

"Govind" wrote:


Hi,

What was your original posting ? Can you post that as well.

Or else stick to your earlier thread to post any comments.

Regards

Govind.

Jules wrote:

Hi

Still can't get this formula to work. It is referencing the column no and
bring the data in that column but is not adding the colums up to that column
no. Ir if if C8 = 9 and the value in col 9 is 4211.00, then the formula is
showing 4211 and not the sum of all the columns up to column 9.



Hi
now that helps :-)
try the following formula:
=SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('tur nover'!$A$1:$A$1000,0,MATCH($C8,'turnover'!$B$1:$ N$1,0)))