dsum or sumproduct ?

[Vass]

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

 

[Guest]

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.

 

[C01d]

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.

 

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

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