| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum the first x cells in a range
Weird one, but I know someone out there knows how to do this...
I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
Try
=SUM(E5:INDEX(E5:P5,COUNTA(E6:P6))) replace COUNTA with COUNT if E6:P6 holds numbers -- Regards, Peo Sjoblom "Robert_L_Ross" wrote in message ... Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
I guess =sum(e5:m5) wouldn't do?
-- Don Guillett SalesAid Software "Robert_L_Ross" wrote in message ... Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
=SUM(OFFSET($E$5,0,0,1,D4))
D4 is count of columns e6:p6 HTH "Robert_L_Ross" wrote: Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
try =sum(e5:L5) with cross-fingers...
regards, driller -- ***** birds of the same feather flock together.. "Robert_L_Ross" wrote: Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
No, because on month three of the year, it would be =sum(e5:g5) while on
month 10 of the year it would be =sum(e5:n5). "Don Guillett" wrote: I guess =sum(e5:m5) wouldn't do? -- Don Guillett SalesAid Software "Robert_L_Ross" wrote in message ... Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
That won't work - all cells have values (each represents a value for a month
in the prior fiscal year). I don't want to sum all of the year, only the same months in the last year that i have values for in this year. "driller" wrote: try =sum(e5:L5) with cross-fingers... regards, driller -- ***** birds of the same feather flock together.. "Robert_L_Ross" wrote: Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
Brilliant! Works like a charm!! Thanks!
"Peo Sjoblom" wrote: Try =SUM(E5:INDEX(E5:P5,COUNTA(E6:P6))) replace COUNTA with COUNT if E6:P6 holds numbers -- Regards, Peo Sjoblom "Robert_L_Ross" wrote in message ... Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
This also works...thanks for another option!
"Toppers" wrote: =SUM(OFFSET($E$5,0,0,1,D4)) D4 is count of columns e6:p6 HTH "Robert_L_Ross" wrote: Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
Note that although this formula is shorter than the index version it is also
volatile so it will always recalculate regardless whether you do any changes or not. That means that if you just open the workbook it will ask if you want to save it when you close it -- Regards, Peo Sjoblom "Robert_L_Ross" wrote in message ... This also works...thanks for another option! "Toppers" wrote: =SUM(OFFSET($E$5,0,0,1,D4)) D4 is count of columns e6:p6 HTH "Robert_L_Ross" wrote: Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum the first x cells in a range
sorry for misunderstanding your lines below...
from your first line: How would I build a function that would sum the first 8 cells in row 5? from your next line : I don't want to sum all of the year, only the same months in the last year that i have values for in this year. regards, driller -- ***** birds of the same feather flock together.. "Robert_L_Ross" wrote: That won't work - all cells have values (each represents a value for a month in the prior fiscal year). I don't want to sum all of the year, only the same months in the last year that i have values for in this year. "driller" wrote: try =sum(e5:L5) with cross-fingers... regards, driller -- ***** birds of the same feather flock together.. "Robert_L_Ross" wrote: Weird one, but I know someone out there knows how to do this... I have two rows, this fiscal year and last fiscal year. I have a count of the cells that have values for this fiscal year (let's say the count of cells e6:p6 is 8). I want to then sum the first 8 cells in row 5 (e5:p5) so I can return last fy to date compared to this fy to date. How would I build a function that would sum the first 8 cells in row 5? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match cells in Range 1 with cells in Range 2 | New Users to Excel | |||
| Find a range of values in a range of cells | Excel Worksheet Functions | |||
| how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
| how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
| Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Linear Mode
