Excel formula generating error

**cherman** · 04-28-2010, 08:59 PM

Below is a formula I'm trying to pass into a cell via Access vba that's generating a Run-time error 1004, Application-defined or object-defined error.

"=SUMPRODUCT(SUBTOTAL(9,OFFSET(IBMT_Raw_Data!$C$2:IBMT_Raw_Data!$C$20000,ROW(IBMT_Raw_Data!$C$2:IBMT_Raw_Data!$C$20000)-ROW(IBMT_Raw_Data!$C$2),0,1)),--(IBMT_Raw_Data!$A$2:IBMT_Raw_Data!$A$20000=A" & lngColumn + 1 & "),--(IBMT_Raw_Data!$D$2:IBMT_Raw_Data!$D$20000=AR7)),--(IBMT_Raw_Data!$E$2:IBMT_Raw_Data!$E$20000<>AR1))"

I added the very last part recently and it was working before.

,--(IBMT_Raw_Data!$E$2:IBMT_Raw_Data!$E$20000<>AR1))

This part and the part right before it are looking at cells on the sheet. In these 2 parts, I'm trying to say "find all records with this value in one column but exclude records with this value in another column".

Any ideas as to why this isn't working?

Thanks!
Clint

**davesexcel** · 04-28-2010, 09:15 PM

Any idea how we could test this for you?

**DonkeyOte** · 04-29-2010, 02:59 AM

Be careful with the parentheses - the closing parenthesis of the prior test was for the function as a whole, given you've added a further test this should be removed.

"=SUMPRODUCT(SUBTOTAL(9,OFFSET(IBMT_Raw_Data!$C$2:$C$20000,ROW(IBMT_Raw_Data!$C$2:$C$20000)-ROW(IBMT_Raw_Data!$C$2),0,1)),--(IBMT_Raw_Data!$A$2:$A$20000=A" & lngColumn + 1 & "),--(IBMT_Raw_Data!$D$2:$D$20000=AR7),--(IBMT_Raw_Data!$E$2:$E$20000<>AR1))"

Note also you don't need to dupe the sheet name for the 2nd range

**cherman** · 04-29-2010, 06:19 PM

DonkeyOte,

This worked like a charm! Thank you for the help.

I have another question for you along the same lines. The formula below has the last 2 parts pointing to the same column. I'm trying to say "find all records that are = AR3 or = AR4". I've tried a few different variations, but no luck.

"=SUMPRODUCT(SUBTOTAL(9,OFFSET(IBMT_Raw_Data!$C$2:$C$20000,ROW(IBMT_Raw_Data!$C$2:$C$20000)-ROW(IBMT_Raw_Data!$C$2),0,1)),--(IBMT_Raw_Data!$A$2:$A$20000=A" & lngColumn + 1 & "),--(IBMT_Raw_Data!$E$2:$E$20000=AR3),--(IBMT_Raw_Data!$E$2:$E$20000=AR4))"

Thanks in advance for your help!

Clint

**DonkeyOte** · 04-30-2010, 02:49 AM

OR's in Arrays and SUMPRODUCT are conducted by means of addition ... ANDs by multiplication.

So to conduct the OR you could replace your existing AND test:

--(IBMT_Raw_Data!$E$2:$E$20000=AR3),--(IBMT_Raw_Data!$E$2:$E$20000=AR4)

with

(IBMT_Raw_Data!$E$2:$E$20000=AR3)+(IBMT_Raw_Data!$E$2:$E$20000=AR4)

no double unary required given the Booleans of each array will be coerced by virtue of the addition.

You can also (if preferred) replace the AND with an ISNUMBER / MATCH test, eg:

--ISNUMBER(MATCH(IBMT_Raw_Data!$E$2:$E$20000,AR3:AR4,0))

Worth bearing possibility of double counting when dealing with ORs

This is (100%) not an issue here given both tests relate to the same cell being = a given value (ie can not be both simultaneously) however on occasion double counting in an OR is very much a reality.

Consider:

=SUMPRODUCT((A1:A10=1)+(B1:B10=1),C1:C10)

if follows that if both A1 & B1 = 1 I end up with a multiplier of 2 (TRUE+TRUE) rather than 1 which would be the intention.

To avoid the double count we can use the SIGN of the OR output to determine the multiplier, eg:

=SUMPRODUCT(--(((A1:A10=1)+(B1:B10=1))>0),C1:C10)

or

=SUMPRODUCT(SIGN((A1:A10=1)+(B1:B10=1)),C1:C10)

(the use of SIGN is seen as being more expensive given the additional function call - I'd use whichever you prefer though)