SUMPRODUCT Vs ARRAY FORMULAS, which is the best and why?

SUMPRODUCT is also array formula, only don't need to be entered with ctrl+shift+enter.

So efficiency is same and that's why array formula must be avoided (if possible) or keeped in minimum ranges.

Actually, I am doubting all my formulas marked in yellow..
In SUMPRODUCT We can never just check within the conditions whether it more than once or not..

Ex: As per the Title for the Logical OR ( Faxes or Jones) I thought its just saying that either of them but its meant to have just either of them and not both of them, Correct

So I modified my SUMPRODUCT formula this way:

PHP Code: 

=SUMPRODUCT(((A2:A10="Fax")*(B2:B10<>"Jones"))*(A2:A10="Fax")+(B2:B10="Jones"))

where I check that the combination of both is not present at the same time ((A2:A10="Fax")*(B2:B10<>"Jones"))
and then the regular either of them.. *(A2:A10="Fax")+(B2:B10="Jones") 


Now for me the LOGICAL XOR also seems to be the same as above..and still getting confused with the last requirement, though I managed to TALLY the answers without understanding the query correctly , I need to understand the subjective part which exact combinations are counted..

Totally confused now, I dont know how to get the last condition for NAND?

Like this?

=SUMPRODUCT(--(A2:A10&B2:B10<>"FaxJones"))

Zbor,

I would really appreciate if you could explain me the requirements from LOGICAL OR till LOGICAL NAND..
Actaully, I am not able to understand which ones are considered for what?

Seriously, it would be great help..

Thanks in advance..

Bump NO Response

Bump No Response

YOu are a Saviour...

HOnestly speaking, I felt I was actually coming to terms with the SUMPRODUCT formula until I ventured into this file..
I actually dont understand what the question is ?

1. Summing Sales: Faxes Sold By Brown

My Understanding- Total Units -Only Faxes and Sold by Mr Brown..

PHP Code: 

=SUMPRODUCT((A2:A10="Fax")*(B2:B10="Brown")*(C2:C10)) 


2.Logical AND (Faxes And Jones)its mentioned incorrectly should be Mr Brown.

My Understanding- Count -Only Faxes and Sold by Mr Brown..

PHP Code: 

=SUMPRODUCT((A2:A10="Fax")*(B2:B10="Brown")) 


Now, I am going Bonkers over here..

3. Logical OR (Faxes Or Jones)

PHP Code: 

Original Formula- {=SUM(IF((A2:A10="Fax")+(B2:B10="Jones"),1,0))} 


My Understanding- Get the Count where the Product is Fax or the Person is Jones

4. Logical XOR (Fax Or Jones but not both)

PHP Code: 

Original Formula- {=SUM(IF(MOD((A2:A10="Fax")+(B2:B10="Jones"),2),1,0))} 


My Understanding- I dont know ?

5. Logical NAND (All Sales Except Fax And Jones)

PHP Code: 

Original Formula- {=SUM(IF((A2:A10="Fax")+(B2:B10="Jones")<>2,1,0))} 


My Understanding- I dont know ?

CAn you please explain ..

Warm REgards
e4excel

No i am not actuallt talking about Formulas at all, I have just tried different combinations and matched the results which according tome is not a good approach..

I mean what do the questions mean? as I have this file I am taking it as a reference:

Logical OR (Faxes Or Jones)


Logical XOR (Fax Or Jones but not both)


Logical NAND (All Sales Except Fax And Jones)

Pretty confused as to what is asked?

Yes exactly ..! as in the original formulas they have used the If condition with 1 and 2..
I have got completely confused , if I dont understand the requirement in words how will i build the criterias in the SUMPRODUCT?

I JUST FIBBED the results or just manipulated the answers but actually did not understand why it was so?

OR is meant like this or that..!

Just provide me an explanation please alingwith the SUMPRODUCT code..

Warm Regards
e4excel

Originally Posted by NBVC

With Sumproduct and Array formulas, the + sign is used for OR and the * is used for AND.... so you have to figure out the right one and/or combination to get the result you are intending....

Order of operations is important... * (AND) gets processed before + (OR) so if you want the + processed first, you need to enclose the argument in parentheses, like in normal math...

The Precedence of "*" before "+" was something I did not know..

But I really meant to understand More about the XOR and NAND Logical Explanation as to what exactly they do, its just not excel formula but I needed to understand the LOGICAL gates..

Is XOR like NOT(OR)
IS NAND like NOT(AND)

Would really appreciate if you could explain me the Present examples for just the three conditions with the regular C+S+E and SUMPRODUCT formula..

Warm REgards
e4excel

Originally Posted by NBVC

So you want to know what this: "Logical AND (Faxes And Jones)" means?

Yeah exactly

Originally Posted by NBVC

AND:

A connective in logic which yields true if all conditions are true, and false if any condition is false.

OR:

A connective in logic which yields true if any one of a sequence conditions is true, and false if all conditions are false. In formal logic, the term disjunction (or, more specifically, inclusive disjunction) is commonly used to describe the OR operator.

NAND:

also known as the Sheffer stroke, is a connective in logic equivalent to the composition NOT AND that yields true if any condition is false, and false if all conditions are true

XOR:

A connective in logic known as the "exclusive or," or exclusive disjunction. It yields true if exactly one (but not both) of two conditions is true.

Thanks again NBVC for the Mathematical definitions..but when I tried to interpret the same in the SUMPRODUCT I couldt quite do the same, lets take OR Gate for example :

The Question was

3. Logical OR (Faxes Or Jones)

PHP Code:
Original Formula- {=SUM(IF((A2:A10="Fax")+(B2:B10="Jones"),1,0))}
My Understanding- Get the Count where the Product is Fax or the Person is Jones

As I mentioned Fibbed the answers, but in my mind, I was thinking of putting this in SUMPRODUCT..

Please Login or Register  to view this content.

so that its either Fax or Jones now when I look at the answer its 7 whereas the one given in the file is 6..

So I didnt understand that? I am really sorry that I have kep this query still open but honestly I am still unsure when I look at the examples..especially these should be something I must be good at..!

I am not able to exactly convey but I think its the interpretaion of the LOGICAL GATES in that example that I am finding difficult..

IF OR was only about getting either one of the said variables as FALSE then why does the SUMPRODUCT change..? Or is it that the SUMPRODUCT all the results seperately and therefore we need to remove that condition from the SUMPRODUCT ?

So can you please provide me the answers with the SUMPRODUCT formula and with some explanation from which I can explain my DUMB head..

Thanks...

Thanks a lot NBVC for the explanation that helps and I am still re-reading it...

But does the XOR mean (that either but not both) and NAND mean (that all except the given conditions)

Sorry I am still taking time but I really do appreciate your patience..

Thanks again..