sumif when not strikeout and secodn column is not 0

I'm trying to figure out how to do a sumif on a column of data and only total the items that are not struckout and that are flagged in a separate column with a 1. The column with the 1 is used for other purposes, so I can't change it and I can't add in another column due to some legacy code that looks for the last column and a count of columns. So, I'm stuck with trying to evaluate the data "in place" insted of using a separate column and sumproduct. I've got a UDF that will evaluate if a cell is struckout, so I've got that much.

Without seeing the udf something like
sumproduct((column with 1=1)*(udf(columnwith strikeout)=false)*(column with strikeout))

eg sumproduct((a2:a100=1)*(udf(b2:b100)=true)*(b2:b100))

or entered as an array
sum(if(and(a2:a100=1,udf(b2:b100)=false),b2:b100,0) entered as an array with shift ctrl enter

or

sum(if(and(a2:a100=1,udf(b2:b100)),0,b2:b100)

Regards

Dav

Still no joy. Here's my test scenario. The *'s are the items that I have struckout in my sheet. I'm getting the dreaded #VALUE! message. Also, I'm having trouble getting the UDF HasStrike to update when the strikeout is applied.


A B

1 4641
1 324
0 4324 *
1 445
0 7654 *
0 135
1 15
1 483
0 3155
1 18
1 753 *
1 4441

=SUMPRODUCT(A1:A12,B1:B12,--hasstrike(B1:B12))

Please Login or Register  to view this content.