Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to add up a number of colums (posted on 29/10/04)
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 |
#2
|
|||
|
|||
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))) |
#3
|
|||
|
|||
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))) |
#4
|
|||
|
|||
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))) |
#5
|
|||
|
|||
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))) |
#6
|
|||
|
|||
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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Number of Column Limitations | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
Count number of shaded cells | Excel Discussion (Misc queries) | |||
What defines number or text | New Users to Excel |