I have an SQL linked database
and am pulling info from this sheet
As I cannot add an additional column
I need a sum to do the following.

2 columns of figures, Qty and Cost price
I need to multiply each row to get total cost per line and return the grand
total

but this does not work as the mutliplication is totalling each column first
Ideally a third column in the SQL data would be the answer but I have no
idea how to add this
automatically as this data is refreshed every 5 mins

my attempt that does not work...

=((DSUM(maindata,"it_cost",$R$1:$S$2)/100)*(DSUM(maindata,"it_quan",$R$1:$S$2)/100))

R1:S2 = Date and criteria
it_cost and it_quan are in '000s hence the /100

Any ideas please ?

thanks

--
Vass

If you can use a pivot table, you can create a calculated field to do that
operation, and it will also calculate the grand totals for you.

Hope this helps,
Miguel.

"Vass" wrote:

> I have an SQL linked database
> and am pulling info from this sheet
> As I cannot add an additional column
> I need a sum to do the following.
>
> 2 columns of figures, Qty and Cost price
> I need to multiply each row to get total cost per line and return the grand
> total
>
> but this does not work as the mutliplication is totalling each column first
> Ideally a third column in the SQL data would be the answer but I have no
> idea how to add this
> automatically as this data is refreshed every 5 mins
>
> my attempt that does not work...
>
> =((DSUM(maindata,"it_cost",$R$1:$S$2)/100)*(DSUM(maindata,"it_quan",$R$1:$S$2)/100))
>
> R1:S2 = Date and criteria
> it_cost and it_quan are in '000s hence the /100
>
> Any ideas please ?
>
> thanks
>
> --
> Vass
>
>
>
>
>
>

Use sumproduct as follows:

=SUMPRODUCT(A1:A19,B1:B19)

You will have to change the references accordingly. The first parameter
is an array of values representing the quantity while the second
parameter is an array of values representing the cost price.


--
C01d
------------------------------------------------------------------------
C01d's Profile: http://www.excelforum.com/member.php...o&userid=34422
View this thread: http://www.excelforum.com/showthread...hreadid=544346

"C01d" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Use sumproduct as follows:
>
> =SUMPRODUCT(A1:A19,B1:B19)
>
> You will have to change the references accordingly. The first parameter
> is an array of values representing the quantity while the second
> parameter is an array of values representing the cost price.
>
>
OK I've tried this
data! being the second sheet holding the data
the first array is a condition that I only want customers = "RETAILAC" to be
included
data!E = quantity
data!W=cost price

=SUMPRODUCT(data!DA2A7199="RETAILAC",data!E2:E7199,data!W2:W7199)

but this returns a 0 value
any clues?
thanks
--
Vass