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

 

[Brian from Maui]

Should have (could have) posted an example earlier.

Book2
	A	B	C	D
1
2	1	1	3	8
3	2	1	1
4	3	1	4
5
6
7
8
9
10		1
11	1
12	2
13	3
Sheet1

I see you're using a non English version, so please make the necessary changes.

 

[cstlaurent]

EUREKA..

I don't know what I was doing wrong before, put I just cut and pace this formula, and everything work just fine, Eaven with text criteria.

Big Thank you for all this.

 

[Brian from Maui]

EUREKA..

I don't know what I was doing wrong before, put I just cut and pace this formula, and everything work just fine, Eaven with text criteria.

Big Thank you for all this.

As an aside, I prefer the comma syntax to the asterisk one

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

 

[KenWright]

cstlaurent - Are you saying this works for you even if you have the text value 'OT' in your column of values, ie column C, because I don't believe it does. In your initial example data you had the value 'OT' (which i assume signified overtime) in column C.

Ken...

 

[Brian from Maui]

cstlaurent - Are you saying this works for you even if you have the text value 'OT' in your column of values, ie column C, because I don't believe it does. In your initial example data you had the value 'OT' (which i assume signified overtime) in column C.

Ken...

Ken,

With the comma syntax, you can have text values in the column to sum. I think what the OP meant was the criterias to sum are text values, but then what do I know!

Book2
	A	B	C	D
1
2	1	1	ot	5
3	2	1	1
4	3	1	4
5
6
7
8
9
10		1
11	1
12	2
13	3
Sheet1

 

[KenWright]

Well I'm b*******

I'm absolutely gobsmacked. All the times and ways i've used that function and never come across that - I stand totally corrected, and thank you very much.

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

 

[cstlaurent]

Everything is just working Find, even with the *. My second criteria does mean Overtime and is text. Honestly I don't know much about the comma -- differcence. But I guess you guys know way better than me...

thanks again

 

[Brian from Maui]

cstlaurent - Are you saying this works for you even if you have the text value 'OT' in your column of values, ie column C, because I don't believe it does. In your initial example data you had the value 'OT' (which i assume signified overtime) in column C.

Ken...

Ken,

Stepping through the arrays, the #VALUE error happens because the first array with Isnumber/Match is not coerced into 1/0's without the --.and results in the error. I don't think it's because the range to sum has text values instead of numeric values.

You can still use the * syntax without the Isnumber/Match and still have text in the range to sum.

Book2
	A	B	C	D
2	1	1	ot	5
3	1	1	1
4	1	1	4
Sheet1

Maybe Aladin or one of the other MVP's will drop by and give a better answer.

 

[NateO]

Hello, there's some disscusion here:

http://www.mrexcel.com/archive2/64900/75139.htm

It certainly does not ignore error returns if you force feed one in there, so the underlying code must be smart enough not to multiply the text by the coerced boolean and not generate the error ...

 

[KenWright]

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...........