DSUM or SUMPRODUCT?

V

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

but this returns a 0 value
any clues?
thanks
 
B

Bob Phillips

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


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
D

daddylonglegs

Try this amendment

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

daddylonglegs

Did you try my suggestion?

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

Bob Phillips

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

Vass

"daddylonglegs" >
Try this amendment

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

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
 
B

Bob Phillips

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

Vass

Bob Phillips said:
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)
Click to expand...
thanks Bob
 
Top