SUMPRODUCT Issue

[CyrusTheVirus]

Hi everyone,

I guess I don't seem to understand the SUMPRODUCT function enough in terms of separation of arrays by a 'comma' or by a 'multiplication' sign. Here's my issue:

I do not understand why when using SUMPRODUCT and a '*' (multiplication sign) to join the array calculations the formula works, but using the ',' (comma) it doesn't work. I thought that the calculation would be the same using either the '*' or the ',' because both are indicating a separation of the arrays.

Interestingly enough: When adding numbers instead of names, it seems to work using either the '*' or the ',' to separate... not sure why it works for numbers and not names?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Reference (A1)[/TD]
[TD]Dan (B1)[/TD]
[/TR]
[TR]
[TD]Reference (A2)[/TD]
[TD]Jones (B2)[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]First Name (A5)[/TD]
[TD]Last Name (B5)[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]Smith[/TD]
[TD]70 = SUMPRODUCT((A6:A9=B1)*(B6:B9=B2))[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]Jones[/TD]
[TD]0 = SUMPRODUCT(A6:A9=B1,B6:B9=B2)[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]Jones[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if clarification is needed. Thanks.

 

[oldbrewer]

you have 4 dans and 2 jones so the answer should be 2, shouldn't it ?

 

[CyrusTheVirus]

you have 4 dans and 2 jones so the answer should be 2, shouldn't it ?

Hi oldbrewer,

My apologies, yes. the first should read 2, and the one underneath should read 0 (which it does).

I'm trying to find out why the one below reads 0, when if I use the same formula for numbers (instead of words) (using a comma OR a multiplication sign), then it works...

 

[gaz_chops]

I think i'm right in saying, when you use multiplication it converts True/False to 1/0, with the comma you need to force the True/False to 1/0 by adding --

=SUMPRODUCT(--(A19:A22=B1),--(B19:B22=B2))

 

[CyrusTheVirus]

I think i'm right in saying, when you use multiplication it converts True/False to 1/0, with the comma you need to force the True/False to 1/0 by adding --

=SUMPRODUCT(--(A19:A22=B1),--(B19:B22=B2))

Apparently you are correct. That worked! Thanks for the help!

Though, I must say that the logic seems a bit strange in this regards to the SUMPRODUCT, but eh, whatever. I'll just keep this in mind. Thanks again.