sumproduct function / VB user defined function

[aw]

Dear all,

I have a formula to sum QTY for 2 criteria [that is product = apple (column
AL) & Vendor = ABC Company (column R)].

=SUMPRODUCT(--($AL$5:$AL211="apple"),--($R$5:$R211=â€ABC Companyâ€),$U5:$U211)


It works great. However after I rewrite the formula by using the range name
(aim to simplify my formula) , it finds error. just replace the range by
range_name i.e.

=SUMPRODUCT(--(my_product="apple"),--(my_vendor=â€ABC Companyâ€),my_QTY)

Remark :
$AL$5:$AL211 = column of “product†= range named “my_productâ€
$R$5:$R211 = column of “vendor†= range named “my_vendorâ€
$U5:$U211 = column of “QTY†= range named “my_QTYâ€

Could anyone can help in this case.

Also, can it be re-writed in simply way by creating user-defined function
under VB?!
Thx a lot!!

 

[JMB]

I think you will need to provide more details about what error you are
receiving. Your formula looks right to me. Perhaps double check your named
ranges and ensure they are correct. Or is the formula evaluating to 0 and
you are expecting something else?

 

[Max]

.. after I rewrite the formula by using the range name .. it finds error ..

=SUMPRODUCT(--(my_product="apple"),--(my_vendor=â€ABC Companyâ€),my_QTY)

My guess is that your ranges is somehow gone out of sync (in size)
Check and correct, and it should work just as well

P/s: Using dynamic ranges need extra care, these have a tendency to go out
of sync. Peg all 3 dynamic ranges to a single col for the height param in the
OFFSET, eg the "my_product" col
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---

 

[Charles Williams]

$AL$5:$AL211 contains a relative reference.
This can cause problems when used in a range name because it can be
difficult to work out what the range name reference is relative to (its
clearer if you switch to R1C1 style).

I would recommend that you use absolute references in your range names.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com