Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how to add up number of columns based on month eg feb = 2 oct = 1.
need to add figures to give total figures to period end each month and do not
want to keep doing it. the figures are fed into the spreadsheet automatically on a monthly vasis |
#2
|
|||
|
|||
Hi
you may provide some more details about your data structure. e.g. post some example rows of data as plain text "cookie" wrote: need to add figures to give total figures to period end each month and do not want to keep doing it. the figures are fed into the spreadsheet automatically on a monthly vasis |
#3
|
|||
|
|||
I have a spreadsheet set up which feed 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. "Frank Kabel" wrote: Hi you may provide some more details about your data structure. e.g. post some example rows of data as plain text "cookie" wrote: need to add figures to give total figures to period end each month and do not want to keep doing it. the figures are fed into the spreadsheet automatically on a monthly vasis |
#4
|
|||
|
|||
Hi
what I want to know is soem exact details (cell references). e.g. - in which sells is the month name (in row 1, etc.) - which cells do you want to sum As said: just post some example data as plain text "cookie" wrote: I have a spreadsheet set up which feed 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. "Frank Kabel" wrote: Hi you may provide some more details about your data structure. e.g. post some example rows of data as plain text "cookie" wrote: need to add figures to give total figures to period end each month and do not want to keep doing it. the figures are fed into the spreadsheet automatically on a monthly vasis |
#5
|
|||
|
|||
Hi
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 "Frank Kabel" wrote: Hi what I want to know is soem exact details (cell references). e.g. - in which sells is the month name (in row 1, etc.) - which cells do you want to sum As said: just post some example data as plain text "cookie" wrote: I have a spreadsheet set up which feed 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. "Frank Kabel" wrote: Hi you may provide some more details about your data structure. e.g. post some example rows of data as plain text "cookie" wrote: need to add figures to give total figures to period end each month and do not want to keep doing it. the figures are fed into the spreadsheet automatically on a monthly vasis |
#6
|
|||
|
|||
Hi
now that helps :-) try the following formula: =SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('turnove r'!$A$1:$A$1000,0,MATCH($C8,'turnover'!$B$1:$N$1,0 ))) "cookie" wrote: Hi 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 "Frank Kabel" wrote: Hi what I want to know is soem exact details (cell references). e.g. - in which sells is the month name (in row 1, etc.) - which cells do you want to sum As said: just post some example data as plain text "cookie" wrote: I have a spreadsheet set up which feed 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. "Frank Kabel" wrote: Hi you may provide some more details about your data structure. e.g. post some example rows of data as plain text "cookie" wrote: need to add figures to give total figures to period end each month and do not want to keep doing it. the figures are fed into the spreadsheet automatically on a monthly vasis |
#7
|
|||
|
|||
Hi
Can't get the formula to work, keep getting N/A even though there is data to sum. Is it possible that I need to have numerical column headings as the 'MATCH' part of the formula seems to be looking for a number to match to. Tx "Frank Kabel" wrote: Hi now that helps :-) try the following formula: =SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('turnove r'!$A$1:$A$1000,0,MATCH($C8,'turnover'!$B$1:$N$1,0 ))) "cookie" wrote: Hi 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 "Frank Kabel" wrote: Hi what I want to know is soem exact details (cell references). e.g. - in which sells is the month name (in row 1, etc.) - which cells do you want to sum As said: just post some example data as plain text "cookie" wrote: I have a spreadsheet set up which feed 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. "Frank Kabel" wrote: Hi you may provide some more details about your data structure. e.g. post some example rows of data as plain text "cookie" wrote: need to add figures to give total figures to period end each month and do not want to keep doing it. the figures are fed into the spreadsheet automatically on a monthly vasis |
#8
|
|||
|
|||
Hi
the value that is in C8 has to be as well in the range B1:N1 on your turnover sheet. e.g. C8: Mar B1:N1: Jan - Feb - Mar - Apr - ... -- Regards Frank Kabel Frankfurt, Germany "cookie" schrieb im Newsbeitrag ... Hi Can't get the formula to work, keep getting N/A even though there is data to sum. Is it possible that I need to have numerical column headings as the 'MATCH' part of the formula seems to be looking for a number to match to. Tx "Frank Kabel" wrote: Hi now that helps :-) try the following formula: =SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('turnove r'!$A$1:$A$1000,0,MAT CH($C8,'turnover'!$B$1:$N$1,0))) "cookie" wrote: Hi 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 "Frank Kabel" wrote: Hi what I want to know is soem exact details (cell references). e.g. - in which sells is the month name (in row 1, etc.) - which cells do you want to sum As said: just post some example data as plain text "cookie" wrote: I have a spreadsheet set up which feed 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. "Frank Kabel" wrote: Hi you may provide some more details about your data structure. e.g. post some example rows of data as plain text "cookie" wrote: need to add figures to give total figures to period end each month and do not want to keep doing it. the figures are fed into the spreadsheet automatically on a monthly vasis |
#9
|
|||
|
|||
Hi Frank
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. "Frank Kabel" wrote: Hi the value that is in C8 has to be as well in the range B1:N1 on your turnover sheet. e.g. C8: Mar B1:N1: Jan - Feb - Mar - Apr - ... -- Regards Frank Kabel Frankfurt, Germany "cookie" schrieb im Newsbeitrag ... Hi Can't get the formula to work, keep getting N/A even though there is data to sum. Is it possible that I need to have numerical column headings as the 'MATCH' part of the formula seems to be looking for a number to match to. Tx "Frank Kabel" wrote: Hi now that helps :-) try the following formula: =SUMIF('turnover'!$A$1:$A$1000,$B8,OFFSET('turnove r'!$A$1:$A$1000,0,MAT CH($C8,'turnover'!$B$1:$N$1,0))) "cookie" wrote: Hi 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 "Frank Kabel" wrote: Hi what I want to know is soem exact details (cell references). e.g. - in which sells is the month name (in row 1, etc.) - which cells do you want to sum As said: just post some example data as plain text "cookie" wrote: I have a spreadsheet set up which feed 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. "Frank Kabel" wrote: Hi you may provide some more details about your data structure. e.g. post some example rows of data as plain text "cookie" wrote: need to add figures to give total figures to period end each month and do not want to keep doing it. the figures are fed into the spreadsheet automatically on a monthly vasis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I WANT TO SET PRINT RANGE TO NUMBER OF COLUMNS | Setting up and Configuration of Excel | |||
How to extract month number from month name | Excel Discussion (Misc queries) | |||
Summarizing of columns for different days of month | Excel Discussion (Misc queries) | |||
how to increase maximum number of columns in excel 2003 | Excel Discussion (Misc queries) | |||
Subtracting based on number of miles | Excel Discussion (Misc queries) |