Hi all.
XP xl2003

After some struggling, I have managed to create the following formula in a
procedure which works fine returning the correct result from a filtered list.
However, I would like to include two more tests if possible before the
result in INV_AMOUNT is returned.

In the RECONCILIATION worksheet I have two cols (B & C) for firstname &
Lastname so in addition to testing for invoice ID in Col E ( named range
INV_APARID in INVOICE sheet ) I also need to test both B10 & C10 on
reconciliation sheet against named ranges INV_FNAME & INV_LNAME in INVOICE
worksheet before INV_AMOUNT is returned. However, I am not too sure how to
include extra tests in the formula?

..Range("G10").Formula = _

"=IF($E10=$E9,0,SUMPRODUCT(SUBTOTAL(3,OFFSET(INV_A PARID,ROW(INV_APARID)-MIN(ROW(INV_APARID)),,1)),--(INV_APARID=RECONCILIATION!$E10),INV_AMOUNT))"


Hope clear - any help / guidance appreciated.
--
dave

Hello Dave,

I use the sumprod function in many apps, so, please consider :
"=IF(cond1,cond1_matched,
SUMPRODUCT(SUBTOTAL(3,OFFSET(INV_APARID,ROW(INV_AP ARID)-MIN(ROW(INV_APARID)),,1)),--(INV_APARID=RECONCILIATION!$E10),INV_AMOUNT)
+SUMPRODUCT(mmmmmmmmmmmmmm)
+SIMPRODUCT(nnnnnnnnnnnnnnnnnnnnnnn)
....
+SUMPRODUCT(yyyyyyyyyyyyyyyyyyyyyyyyyyyy)
)"

I also have a pdf which I could send to you about SUMPROD

regards,
Hervé+

--
Herve Hanuise
http://www.affordsol.be


"dave" wrote:

Hi all.
XP xl2003

After some struggling, I have managed to create the following formula in a
procedure which works fine returning the correct result from a filtered list.
However, I would like to include two more tests if possible before the
result in INV_AMOUNT is returned.

In the RECONCILIATION worksheet I have two cols (B & C) for firstname &
Lastname so in addition to testing for invoice ID in Col E ( named range
INV_APARID in INVOICE sheet ) I also need to test both B10 & C10 on
reconciliation sheet against named ranges INV_FNAME & INV_LNAME in INVOICE
worksheet before INV_AMOUNT is returned. However, I am not too sure how to
include extra tests in the formula?

.Range("G10").Formula = _

"=IF($E10=$E9,0,SUMPRODUCT(SUBTOTAL(3,OFFSET(INV_A PARID,ROW(INV_APARID)-MIN(ROW(INV_APARID)),,1)),--(INV_APARID=RECONCILIATION!$E10),INV_AMOUNT))"


Hope clear - any help / guidance appreciated.
--
dave

thanks for reply,
offer of PDF very kind but given I have been struggling with SumProduct I
was hoping some kind person would be kind enough to help me complete the
formula.
--
dave


"affordsol" wrote:

Hello Dave,

I use the sumprod function in many apps, so, please consider :
"=IF(cond1,cond1_matched,
SUMPRODUCT(SUBTOTAL(3,OFFSET(INV_APARID,ROW(INV_AP ARID)-MIN(ROW(INV_APARID)),,1)),--(INV_APARID=RECONCILIATION!$E10),INV_AMOUNT)
+SUMPRODUCT(mmmmmmmmmmmmmm)
+SIMPRODUCT(nnnnnnnnnnnnnnnnnnnnnnn)
...
+SUMPRODUCT(yyyyyyyyyyyyyyyyyyyyyyyyyyyy)
)"

I also have a pdf which I could send to you about SUMPROD

regards,
Hervé+

--
Herve Hanuise
http://www.affordsol.be


"dave" wrote:

Hi all.
XP xl2003

After some struggling, I have managed to create the following formula in a
procedure which works fine returning the correct result from a filtered list.
However, I would like to include two more tests if possible before the
result in INV_AMOUNT is returned.

In the RECONCILIATION worksheet I have two cols (B & C) for firstname &
Lastname so in addition to testing for invoice ID in Col E ( named range
INV_APARID in INVOICE sheet ) I also need to test both B10 & C10 on
reconciliation sheet against named ranges INV_FNAME & INV_LNAME in INVOICE
worksheet before INV_AMOUNT is returned. However, I am not too sure how to
include extra tests in the formula?

.Range("G10").Formula = _

"=IF($E10=$E9,0,SUMPRODUCT(SUBTOTAL(3,OFFSET(INV_A PARID,ROW(INV_APARID)-MIN(ROW(INV_APARID)),,1)),--(INV_APARID=RECONCILIATION!$E10),INV_AMOUNT))"


Hope clear - any help / guidance appreciated.
--
dave