Sumproduct using one range criteria and one fixe cell

[cstlaurent]

HI am trying to use the sumproduct using two criteria, wich it's ok up to now. But when one of the criteria as took compare the data to a range it is not working. EX: =+Sumproduct((A2:A7=(A20:a24)*(B2:B7=B20)*(C2:C7)).
I am able to use an array formula.. sum(if(or(A2:A6=A20;A2:A6=A21; and so and so);if(B2:B6=B20;C2:C6))). But was hoping there will be a simpler way using a range instead of indicating cell by cell the comparaison with the sumproduct funciton

thanks in advance

20001 REG 7.5
20001 ADD OT
20001 REG 7.5
20002 REG 7.5
20003 REG 7.5


range Where
20001 REG
20003
and so
and so

 

[NateO]

Hello,

=SUMPRODUCT((ISNUMBER(MATCH(A2:A7,A11:A15,0)))*(B2:B7=B10),(C2:C7))
Works fine - Value range containing text with no coercion operators, merely comma syntax.

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,A11:A15,0))),--(B2:B7=B10),(C2:C7))
Works fine.

If I'm repeating you Ken, I apologize.

=sumproduct((ISNUMBER(MATCH(A2:A7,A11:A15,0)))*(B2:B7=B10))

Looks like:

{=sum((ISNUMBER(MATCH(A2:A7,A11:A15,0)))*(B2:B7=B10))}

You're forcing multiplication with no error trapping, but as they arrays are booleans you're fine, multiply all you want.

Now, without seeing the C-code or speaking C for that matter, but based on observation, while you're still multiply matrixes/arrays there must be an extra level of error trapping in the underlying code when specifying the arrays as arguments.

Something along that replaces text strings in the argument matrixes with a zero prior to multiplying, which would seem easy enough actually (VBA e.g., TypeName(element(i)) = "String") ...

 

[Brian from Maui]

LOL - At the risk of ending up eating humble pie again:-

Assuming formula arguments identical to before but merely variations on syntax, and with a value of 'OT' in any of the cells in Col C

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,A11:A15,0))),(B2:B7=B10)*(C2:C7))
Errors with #VALUE - ISNUMBER... coerced via double unary, but value range contains text and is coerced via star.

=SUMPRODUCT((ISNUMBER(MATCH(A2:A7,A11:A15,0)))*(B2:B7=B10),(C2:C7))
Works fine - Value range containing text with no coercion operators, merely comma syntax.

=SUMPRODUCT((ISNUMBER(MATCH(A2:A7,A11:A15,0))),(B2:B7=B10),(C2:C7))
Doesn't error but returns 0 as neither of first two arguments based on criteria have been coerced.

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,A11:A15,0))),(B2:B7=B10),(C2:C7))
Doesn't error but returns 0 as second argument based on criteria has not been coerced.

=SUMPRODUCT((ISNUMBER(MATCH(A2:A7,A11:A15,0))),--(B2:B7=B10),(C2:C7))
Doesn't error but returns 0 as first argument based on criteria has not been coerced.

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,A11:A15,0))),--(B2:B7=B10),(C2:C7))
Works fine.

The error comes from any attempt at coercion of text, eg with the text abc in cell A1, in any other cell =A1*1 will error with #VALUE.

Ken...........

This is getting interesting.....

I always used the rule of using either ,-- or * with no combinations in the formula. The only difference being, using the * with vectors...I think! in the range to sum was not the same size as that of the criterias. I thought that was the "PROPER" syntax. But it seems, you can use a combination of both ,-- and * to get the results. I'm more confused than ever!

 

[KenWright]

Hi Nate - Agreed there. My point though was that in this case it is the attempted coercion of the text value 'OT' in Col C that would error out the formula, eg

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,A11:A15,0))),--(B2:B7=B10),(C2:C7))
Works fine, BUT, if i vary just that final argument and attempt coercion via double unary say, eg

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,A11:A15,0))),--(B2:B7=B10),--(C2:C7))

Then it immediately errors out.

Brian - I've almost always exclusively used star syntax, except where a single argument needs coercion in which case i'll use double unary, and that's solely because I like the look of the stars - daft i know but true

Ken.......

 

[NateO]

Hello again Ken, and welcome to the board incidentally.

Yep, that's why I think there's a test on the element's data type prior to multiplication, as the function's code doesn't appear to sweep out the errors following multipication.

But, there's no need to coerce the third column, just the booleans, yes-no? Unless you had something like this:

=SUMPRODUCT({TRUE,TRUE,TRUE}*{1,1,1},--({2,"2",1}))

But this strikes me as a different problem; an abnormal data set/an attempt at insane computing...

 

[Aladin Akyurek]

[...]
This is getting interesting.....

I always used the rule of using either ,-- or * with no combinations in the formula. The only difference being, using the * with vectors...I think! in the range to sum was not the same size as that of the criterias. I thought that was the "PROPER" syntax. But it seems, you can use a combination of both ,-- and * to get the results. I'm more confused than ever!

If the range to sum is a matrix (not a vector), the comma syntax won't do unless you re-express it. Thus:

=SUMPRODUCT((A2:A6="a")*(B2:B6=4)*(C2:D6))

is well-formed. As long as C2:D6 does not contain any text value, this formula will succeed to return a multi-conditional sum. Since the matrix bit has just 2 columns, this formula can be re-expressed in comma syntax:

=SUMPRODUCT(--(A2:A6="a"),--(B2:B6=4),(C2:C6+D2:D6))

This will also error out when C2:C6 or D2:D6 or both of them contain text, due to the presence of +. In the former, the culprit is *. One just cannot do arithmetics when one of the operands is not numeric.

If all terms are vectors, we have a choice:

Invoke the comma syntax when terms are equally sized vecors. This currently requires EXPLICIT coercion vis-a-vis conditional terms:

SUMPRODUCT(--Conditional,--Conditional,...,VectorToSum)

SUMPRODUCT(Conditional+0,Conditional+0,...,VectorToSum)

Coercers other than -- and +0 are also possible, though not worth enumerating for these two coercers are fastest.

Benefits:

(a) The range to sum can contain text values like formula-blanks. Such values won't thwart the formula.

(b) The comma syntax is faster than the 'star' syntax, which can also be used with equally sized terms:

SUMPRODUCT(Conditional*Conditional*VectorToSum)

Note that the numbering/indexing of conditionals are omitted for convenience.

If the range to sum is a matrix (multiple columns/rows), we have to invoke the star syntax:

SUMPRODUCT(Conditional*Conditional*MatrixToSum)

MatrixToSum should not contain text values.

 

[KenWright]

LOL - Brian, I spend my life in a state of confusion over something or other

Nate - many thanks for the welcome - Hadn't seen Aladin for a while in my neck of the woods, so figured I'd come to his place

Aladin, Thanks for that, especially the vector vs matrix example.

Jon - Hi to you

cstlaurent - Hope we haven't bored you there, but I'm one of those sad muppets that would rather natter about this than go down the pub (yes I know this is hard to believe, but nevertheless.......)

Ken...........

 

[cstlaurent]

Thanks AGAIN to ALL, I never beleived that my guestion will create so much discussion..

You guys are great. Now I now where to find answer praticaly to all if not to all.

Have i nice Year...

 

[Brian from Maui]

LOL - Brian, I spend my life in a state of confusion over something or other

Nate - many thanks for the welcome - Hadn't seen Aladin for a while in my neck of the woods, so figured I'd come to his place

Aladin, Thanks for that, especially the vector vs matrix example.

Jon - Hi to you

cstlaurent - Hope we haven't bored you there, but I'm one of those sad muppets that would rather natter about this than go down the pub (yes I know this is hard to believe, but nevertheless.......)

Ken...........

Ken,

You wouldn't be this Ken Wright would you?

http://www.cs.ncl.ac.uk/research/pubs/authors/byType.php?id=26

 

[KenWright]

LOL - I wish I knew enough to write all that stuff, but no that's not me. You'll generally find me in any of the Microsoft Excel Newsgroups or on Tek-Tips or VBAExpress.

Ken.............