Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
cookie
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
cookie
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
cookie
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
cookie
 
Posts: n/a
Default

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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Jules
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I WANT TO SET PRINT RANGE TO NUMBER OF COLUMNS ckbusia Setting up and Configuration of Excel 2 January 20th 05 08:47 PM
How to extract month number from month name PM Excel Discussion (Misc queries) 2 January 19th 05 04:07 PM
Summarizing of columns for different days of month Charles Excel Discussion (Misc queries) 4 January 18th 05 05:07 PM
how to increase maximum number of columns in excel 2003 [email protected] Excel Discussion (Misc queries) 1 January 16th 05 09:13 PM
Subtracting based on number of miles Ms Chewie Excel Discussion (Misc queries) 3 December 21st 04 06:35 AM


All times are GMT +1. The time now is 06:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"