DSUM or SUMPRODUCT?

[Vass]

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

 

[Bob Phillips]

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


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[daddylonglegs]

Try this amendment

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

 

[Vass]

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

returns #VALUE!

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

 

[daddylonglegs]

Did you try my suggestion?

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

 

[Bob Phillips]

Read it as a test

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


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Vass]

"daddylonglegs" >

Try this amendment

=SUMPRODUCT(--(data!DA2A7199="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

 

[Bob Phillips]

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

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


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Vass]

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

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

thanks Bob