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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:

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

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)))

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Monday, 22 November 2021 at 10:16:51 pm UTC+11, poopall wrote:

On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:

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

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)))

Sorry should be:

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:

On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:

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

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)))

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(....

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:

On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:

On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:

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

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)))

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(....

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:

On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:

On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:

On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:

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

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)))

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(....

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

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'.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:

On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:

On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:

On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:

On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:

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

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)))

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(....

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

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'.

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 ?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, December 3, 2021 at 10:04:34 AM UTC+5:30, poopall wrote:

On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:

On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:

On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:

On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:

On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:

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

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)))

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(....

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

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'.

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 ?

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...)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, 3 December 2021 at 6:52:19 pm UTC+11, timec...@gmail.com wrote:

On Friday, December 3, 2021 at 10:04:34 AM UTC+5:30, poopall wrote:

On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:

On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:

On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:

On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:

On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:

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

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)))

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(....

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

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'.

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 ?

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...)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Monday, December 6, 2021 at 11:07:19 AM UTC+5:30, poopall wrote:

On Friday, 3 December 2021 at 6:52:19 pm UTC+11, timec...@gmail.com wrote:

On Friday, December 3, 2021 at 10:04:34 AM UTC+5:30, poopall wrote:

On Thursday, 2 December 2021 at 3:45:46 pm UTC+11, timec...@gmail.com wrote:

On Wednesday, December 1, 2021 at 4:14:17 PM UTC+5:30, poopall wrote:

On Tuesday, 30 November 2021 at 5:56:19 pm UTC+11, timec...@gmail.com wrote:

On Monday, November 22, 2021 at 4:46:51 PM UTC+5:30, poopall wrote:

On Wednesday, 10 November 2021 at 4:49:05 pm UTC+11, timec...@gmail.com wrote:

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

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)))

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(....

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

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'.

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 ?

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...)

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.
*
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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

[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...')

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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 })
*

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)