excel: sum of diff values of same cell in same cell

[timepro timesheet]

hello all:

how to get SUM for a cell with more than 1 value, were each value is summed in that same cell.

coding:
TRY
oexcel=createobject('excel.application')
CATCH
mymessage(23,39,'excel application not installed/activated in this system',xm,xy,fcn,6,6,900)
dla6(24,39,'this report may crash',xm,xy,fcn,6,6,900);inkey(x)
END
oexcel=createobject('excel.application')
oexcel:workbooks:add()
osheet=oexcel:activesheet
...
...
do whil !eof()
...
for xxxx=1 to if(month=1.or.month=1.or.month=3.or.month=5.or.month=7.or.month=8.or.month=10.or.month=12,31,30)
if y1$'Ii';hrsandcharges=str(thisdayhrs[xxxx],2)+CHR(10)+str(thisdaycost[xxxx],8);end
cmemo+=chr(ktab)+if(y1$'Ii',["]+alltrim(hrsandcharges)+["],str(thisdayhrs[xxxx],2))
next
skip
endd
...
...
*after processing
GTSetClipboard(cMemo)
xsum(fromcell, tocell)
*in the same cell, should list different sums for both the thisdayhrs & thisdaycost of the same cell.

*********
func xsum(xs1,xs2)
*********
for xx=xs1 to xs2
oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)):Address( .F.,.F.)+")"
next

*thank you

[poopall]

I don't follow exactly what you are looking for, but maybe you should use subtotal(9,x,y). When you finally do a sum of a column or row, the subtotals are ignored,

Can you give an example of what your spreadsheet looks like, also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.

oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))

[poopall]

Sorry should be:

oSheet:Cells(xrow,xx):Formula = "=SUM(oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx)))

[timepro timesheet]

thanks poopall:

let me rephrase my query:

in a 'single' cell, i have separate figures for 'cost & discount & tax'
e.g.: from cell D6 to D134 value=str(cost)+chr(10)+str(disc)+chr(10)+str(tax) [single cell will list 3 separate figures in 3 separate rows]
at end of my loop/process, with what formula/sum, can i get individual totals/sum (D6-D134) of 'cost, disc, tax' in the same cell (3 separate rows, but in same/single cell)

also, if the user edits discount in cell D110 , only the sum of discount should auto change.

thanks

p.s.
'also I don't understand the :Address( .F.,.F.) part of the formula, I would suggest you drop it off and it should work OK.'

sure, will try without address(....

[poopall]

Can you send an example of what you want the spreadsheet to look like

[timepro timesheet]

hi

totals of 'cost, disc, tax' in the same/single cell

i.e. cell D135 should show *separate/individual* sums of cost,disc,tax (D6-D134) as 3 separate rows. (cost,disc,tax are 3 diff. variables)

*e.g.: in D135
1334345 (1334345 being sum of cost D6-D134)
554567 (554... being sum of disc D6-D134)
67418884 (6741... being disc of tax D6-D134)
*
thanks

p.s. oSheet:Cells(xrow,xx):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,xx),oSheet:Cells(xrow-1,xx))+")"
sums only the 'cost'.

[poopall]

Whats the delimiter of the individual cells have you used a comma, as you state you have 3 seperate amounts in there I assume stored in the cell as a string ?

[timepro timesheet]

str(cost,n)+chr(10)+str(disc,n)+chr(10)+str(tax,n)

so after process (creation of the .xlsx file), each cell shows:
12345
6789
1123666
in a single cell. (12345 being cost, 6789 being disc...)

[poopall]

I think the process would be difficult in excel, as you would first have a write a formulae to break up this string into each element, using something like instr() function in excel to locate the delimiter chr(10), then use left , mid and right to break them up, then convert to numeric and then do something else

Is there a reason why each value of cost, disc, tax cannot be written to separate cells, even if these are to another sheet in the same workbook, and then everything else would be relatively easy. The spreadsheet could always be hidden or locked if you did not users to see the content.

[timepro timesheet]

*
thanks poopall for the efforts:

if each value is saved to an independent cell, the columns (already 42) will increase making it 'not easy' for the user to navigate.
besides seeing all the values (cost, disc, tax, qty...) in the same cell in the same eyeshot, makes it easy for the user to discern.

[timepro timesheet]

[horiz total]
oSheet:Cells(val(left(htot[jc],7)),28):Value="=SUM("+oSheet:Range(oSheet:Cells(val(left(htot[jc],7)),7),oSheet:Cells(val(left(htot[jc],7)),9)):Address( .F.,.F.)+")" && horiz total, cell28=cell7+cell8+cell9
how to code:
cell28=cell7-cell8+cell9
cell28=cell7-cell8-cell9
cell28=cell7-(cell8*cell9)
cell28=(cell7/abc)*((cell8/def)*(cell9)+(cell22))

[column totals (vertical)]
oSheet:Cells(xrow,7):Value="=SUM("+oSheet:Range(oSheet:Cells(nstart,14),oSheet:Cells(xrow-1,17)):Address( .F.,.F.)+")"
how to code:
cell7=cell7-cell3+cell22
cell14=cell5-(cell3*cell22)
cellxx=cell19/cell5

regards

p.s.:
i will remove the 'address(.f...'
sometimes back, a guru like you had sent me an example of the 'sum' template with 'address(.f..)' so i simply incorporated it...(albeit, it does not have any adverse effect on the output, so i reckoned, 'if it aint broke...')

[timepro timesheet]

this works, but i need the row,col to be variables:
temp1="E"+alltrim(str(xrow,6)) ; temp2="J"+alltrim(str(xrow,6))

oSheet:cells(xrow,29):formula:="=sum(&temp1:&temp1)-sum(&temp2:&temp2)"

but i need the row,col to be variables (this crashes when writing to xcel)
oSheet:Cells(xrow,xx):formula:="=SUM("+osheet:range(oSheet:cells(xrow,yy),oSheet:cells(xrow,yy))+")"+" - "+"SUM("+oSheet:range(osheet:Cells(xrow,zz),oSheet:Cells(xrow,zz))+")"
*
Error BASE/1081 Argument error: + Arguments: ( [ 1] = Type: C Val: =SUM( [ 2] =|
Type: O Val: { TOLEAUTO Object })
*