DSUM or SUMPRODUCT?
 

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!DA2:DA7199="RETAILAC",data!E2:E71 99,data!W2:W7199)

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

DSUM or SUMPRODUCT?
 

=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),--(data!E2:E7199,data!W2:W7199))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Vass" wrote in message
. uk...
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!DA2:DA7199="RETAILAC",data!E2:E71 99,data!W2:W7199)

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

DSUM or SUMPRODUCT?
 

Try this amendment

=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),data!E2:E7199,data!W2 :W7199)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=544661

DSUM or SUMPRODUCT?
 

"Bob Phillips" wrote
=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),--(data!E2:E7199,data!W2:W7199))

returns #VALUE!

I can see what your doing but can't fix it
--
Vass

DSUM or SUMPRODUCT?
 

Did you try my suggestion?

=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),data!E2:E7199,data!W2
:W7199)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=544661

DSUM or SUMPRODUCT?
 

Read it as a test

=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),data!E2:E7199,data!W2 :W7199)


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Vass" wrote in message
...


"Bob Phillips" wrote

=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),--(data!E2:E7199,data!W2:W7199))

returns #VALUE!

I can see what your doing but can't fix it
--
Vass

DSUM or SUMPRODUCT?
 

"daddylonglegs"
Try this amendment

=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),data!E2:E7199,data!W2 :W7199)


OK this does return a value thank you
Is this result column E * W row by row or does this formula total E then *
by W which obviously is an incorrect result?

also, my Qty and Cost data is recorded in '000s and I'd like to /100 if at
all possible

Thanks in advance
--
Vass

DSUM or SUMPRODUCT?
 

It does each E by each W where DA satisfies the condition.

=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),data!E2:E7199/1000,data!W2:W7199/
1000)


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Vass" wrote in message
...
"daddylonglegs"
Try this amendment

=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),data!E2:E7199,data!W2 :W7199)


OK this does return a value thank you
Is this result column E * W row by row or does this formula total E then *
by W which obviously is an incorrect result?

also, my Qty and Cost data is recorded in '000s and I'd like to /100 if at
all possible

Thanks in advance
--
Vass

DSUM or SUMPRODUCT?
 

"Bob Phillips" wrote in message
...
It does each E by each W where DA satisfies the condition.

=SUMPRODUCT(--(data!DA2:DA7199="RETAILAC"),data!E2:E7199/1000,data!W2:W7199/
1000)


thanks Bob