Strange SUMPRODUCT result

[twaccess]

I thought from an earlier thread that * and + within the sumproduc
formula represented AND / OR.

I was trying this out on the attached file and I get a strange resul
when I use the + as an OR. It returns a value of 5 when it shoul
return 0

Can anyone advise please ?

Thanks


Terr

Attachment filename: twaccess_sumproduct_query.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=49191

 

[Bob Phillips]

It seems to be correct to me. In English the formula you have is

Sum all instances of terry or janet [A1:A8={"terry","janet"})]
OR all instances of rubber multiplied by their values

(A) There are 3 terry and 2 janet which gives 5
(B) There are 0 rubber

Total of A OR B = 5. In other words, you are counting separately.

Similarly for the second.

Why do you think it should be 0.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[twaccess]

Thanks Bob. You make an interesting point and you are helping m
understanding of this function.

I thought any results involving text returned a zero value. Hence th
last part of the formula refers to the list of values where the formul
would total the values that comply with the criteria in the first par
of the formula.

I find that if I remove the OR(+) and put an AND(*) in. I get th
correct result with no added 5.

So making this more puzzling..

Terr

 

[Bob Phillips]

That is correct. There are no items where column A = terry or janet and the
corresponding entry in column B is rubber. This is what AND means.

Do you really understand how SUMPRODUCT works? If not, take a look here for
Ken Wright's explanation http://tinyurl.com/3b868

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[twaccess]

No, I dont thoroughly understand SUMPRODUCT but I'm sufficiently excite
by its possibilities that I want to learn more about it.

So, thanks for the link. I'll check it out.

Terr

 

[JulieD]

Hi Terry

another explaination of SUMPRODUCT that is worth looking at in addition to
Ken Wright's is Aladin Akyurek's at
http://www.mrexcel.com/wwwboard/messages/8961.html

Cheers
JulieD