In order *not* to confuse SUMPRODUCT, we use semicolon as argument separator when comma is used as digit grouping symbol.

e.g.

=SUMPRODUCT(--(A1:A10=123,45);--(B1:B10="abc");C1:C10)

However, we use the plus sign (+) to indicate both OR and ADDITION.

e.g.

+ used for "addition"

=SUMPRODUCT(--(C2:C31="TLG"),--(H2:H31="TLG"),D2:D31+I2:I31)

+ used for "or"

=SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0))

I guess SUMPRODUCT knows that the "+" means "or" because of the equal sign (i.e. logical test)? Why not use double plus (++) for "or" test? Just a thought. Don't mean to confuse anyone.

I find all this interesting.

Epinn

Because we do not get to choose.

The comma, semi-colon separator is driven by the software responding to the
regional settings.

SUMPRODUCT doesn't know anything, it doesn't know -- or *, these are just
processed as arithmetic operators by Excel's floating point engine.
Similarly, it has no idea that + is an OR condition, with or without an
equal. + is another arithmetic operator and is simply treated as such. As
you know, the conditional tests return an array of TRUE/FALSE. If you +
these Booleans, it coerces it to 1 or 0 just as -- does, just as * does.
Type =TRUE+TRUE in a cell, you will see you get 2, =FALSE+FALSE gives 0,
=TRUE+FALSE gives 1.

So, we cannot determine what the syntax is, all we can do is identify what
does work and what doesn't.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
In order *not* to confuse SUMPRODUCT, we use semicolon as argument separator
when comma is used as digit grouping symbol.

e.g.

=SUMPRODUCT(--(A1:A10=123,45);--(B1:B10="abc");C1:C10)

However, we use the plus sign (+) to indicate both OR and ADDITION.

e.g.

+ used for "addition"

=SUMPRODUCT(--(C2:C31="TLG"),--(H2:H31="TLG"),D2:D31+I2:I31)

+ used for "or"

=SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0))

I guess SUMPRODUCT knows that the "+" means "or" because of the equal sign
(i.e. logical test)? Why not use double plus (++) for "or" test? Just a
thought. Don't mean to confuse anyone.

I find all this interesting.

Epinn

Hi

It has nothing to do with Sumproduct per se.
It has everything to do with Logic and Boolean operators.

Consider cells A1, B1 and C1.
We could write
=IF(AND(A1="A",B1="A",C1="A"),TRUE,FALSE)
Equally we could write that more simply as
=AND(A1="A",B1="A",C1="A")
which would return a value of TRUE if ALL 3 conditions were met or FALSE
if ANY was not met.
This could be written as
=((A1="A")*(B1="A")*(C1="A")0)
So we would have (TRUE or FALSE)*(TRUE or FALSE)*(TRUE or FALSE)
which becomes (0 or 1)*(0 or 1)*(0 or 1)
where ANY of those conditions returning 0, will result in the whole
formula returning 0 (FALSE) because they are Multiplied together which
is the same as AND'ing them together. It would require 3 * 1's (TRUES )
to give a result of 1 (TRUE).
In this scenario, the final value to the left of the "" operator can
only be 0 or 1.

Alternatively
=IF(OR(A1="A",B1="A",C1="A"),TRUE,FALSE)
which expressed more simply is
=OR(A1="A",B1="A",C1="A")
which would return a value of TRUE if ANY condition was met or FALSE if
ALL were not met.
Again, this could be written as
=((A1="A")+(B1="A")+(C1="A"))0
which resolves to (0 or 1)+(0 or 1)+(0 or 1)
where ANY of those conditions returning 1, will result in the whole
formula returning at least 1 (TRUE) because they are ADDED together
which is the same as OR'ing
In this scenario, the final value to the left of the " operator can be
0,1,2,or 3 where 0 is FALSE and 1, 2 or 3 is TRUE
--
Regards

Roger Govier


"Epinn" wrote in message
...
In order *not* to confuse SUMPRODUCT, we use semicolon as argument
separator when comma is used as digit grouping symbol.

e.g.

=SUMPRODUCT(--(A1:A10=123,45);--(B1:B10="abc");C1:C10)

However, we use the plus sign (+) to indicate both OR and ADDITION.

e.g.

+ used for "addition"

=SUMPRODUCT(--(C2:C31="TLG"),--(H2:H31="TLG"),D2:D31+I2:I31)

+ used for "or"

=SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0))

I guess SUMPRODUCT knows that the "+" means "or" because of the equal
sign (i.e. logical test)? Why not use double plus (++) for "or" test?
Just a thought. Don't mean to confuse anyone.

I find all this interesting.

Epinn

Bob and Roger,

Thanks for straightening me out. Guess I have g _ _ _ _ _ up big time. Glad I found out at this early stage of learning. It is helpful for me to see the similar formulae grouped together and explained. I appreciate that.

Yes, it has nothing to do with SUMPRODUCT. When I first learned about double negating, coercing etc., SUMPRODUCT was the example. So, I thought it only happened to SUMPRODUCT. Subsequently, when I found out I could use double negating in VLOOKUP when the data type of the lookup value and the array didn't match, I was "surprised" and realized that I had the wrong impression.

So, when we include plus (+) for "or" in a formula, an addition actually takes place. I think I am okay with *, -- and +. I won't worry about ^ and N( ).

Thanks again for a valuable lesson and I know this kind of foundation is important for me down the road.

Cheers,

Epinn

"Roger Govier" wrote in message ...
Hi

It has nothing to do with Sumproduct per se.
It has everything to do with Logic and Boolean operators.

Consider cells A1, B1 and C1.
We could write
=IF(AND(A1="A",B1="A",C1="A"),TRUE,FALSE)
Equally we could write that more simply as
=AND(A1="A",B1="A",C1="A")
which would return a value of TRUE if ALL 3 conditions were met or FALSE
if ANY was not met.
This could be written as
=((A1="A")*(B1="A")*(C1="A")0)
So we would have (TRUE or FALSE)*(TRUE or FALSE)*(TRUE or FALSE)
which becomes (0 or 1)*(0 or 1)*(0 or 1)
where ANY of those conditions returning 0, will result in the whole
formula returning 0 (FALSE) because they are Multiplied together which
is the same as AND'ing them together. It would require 3 * 1's (TRUES )
to give a result of 1 (TRUE).
In this scenario, the final value to the left of the "" operator can
only be 0 or 1.

Alternatively
=IF(OR(A1="A",B1="A",C1="A"),TRUE,FALSE)
which expressed more simply is
=OR(A1="A",B1="A",C1="A")
which would return a value of TRUE if ANY condition was met or FALSE if
ALL were not met.
Again, this could be written as
=((A1="A")+(B1="A")+(C1="A"))0
which resolves to (0 or 1)+(0 or 1)+(0 or 1)
where ANY of those conditions returning 1, will result in the whole
formula returning at least 1 (TRUE) because they are ADDED together
which is the same as OR'ing
In this scenario, the final value to the left of the " operator can be
0,1,2,or 3 where 0 is FALSE and 1, 2 or 3 is TRUE
--
Regards

Roger Govier


"Epinn" wrote in message
...
In order *not* to confuse SUMPRODUCT, we use semicolon as argument
separator when comma is used as digit grouping symbol.

e.g.

=SUMPRODUCT(--(A1:A10=123,45);--(B1:B10="abc");C1:C10)

However, we use the plus sign (+) to indicate both OR and ADDITION.

e.g.

+ used for "addition"

=SUMPRODUCT(--(C2:C31="TLG"),--(H2:H31="TLG"),D2:D31+I2:I31)

+ used for "or"

=SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0))

I guess SUMPRODUCT knows that the "+" means "or" because of the equal
sign (i.e. logical test)? Why not use double plus (++) for "or" test?
Just a thought. Don't mean to confuse anyone.

I find all this interesting.

Epinn

No problem Epinn. Actually I have found these exchanges very useful as I
give teaching sessions on complex counting and summing, and in my ignorance
I never saw things in the same way that you did. I was making some big
assumptions. Hopefully I can incorporate these lessons back in.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob and Roger,

Thanks for straightening me out. Guess I have g _ _ _ _ _ up big time.
Glad I found out at this early stage of learning. It is helpful for me to
see the similar formulae grouped together and explained. I appreciate that.

Yes, it has nothing to do with SUMPRODUCT. When I first learned about
double negating, coercing etc., SUMPRODUCT was the example. So, I thought
it only happened to SUMPRODUCT. Subsequently, when I found out I could use
double negating in VLOOKUP when the data type of the lookup value and the
array didn't match, I was "surprised" and realized that I had the wrong
impression.

So, when we include plus (+) for "or" in a formula, an addition actually
takes place. I think I am okay with *, -- and +. I won't worry about ^ and
N( ).

Thanks again for a valuable lesson and I know this kind of foundation is
important for me down the road.

Cheers,

Epinn

"Roger Govier" wrote in message
...
Hi

It has nothing to do with Sumproduct per se.
It has everything to do with Logic and Boolean operators.

Consider cells A1, B1 and C1.
We could write
=IF(AND(A1="A",B1="A",C1="A"),TRUE,FALSE)
Equally we could write that more simply as
=AND(A1="A",B1="A",C1="A")
which would return a value of TRUE if ALL 3 conditions were met or FALSE
if ANY was not met.
This could be written as
=((A1="A")*(B1="A")*(C1="A")0)
So we would have (TRUE or FALSE)*(TRUE or FALSE)*(TRUE or FALSE)
which becomes (0 or 1)*(0 or 1)*(0 or 1)
where ANY of those conditions returning 0, will result in the whole
formula returning 0 (FALSE) because they are Multiplied together which
is the same as AND'ing them together. It would require 3 * 1's (TRUES )
to give a result of 1 (TRUE).
In this scenario, the final value to the left of the "" operator can
only be 0 or 1.

Alternatively
=IF(OR(A1="A",B1="A",C1="A"),TRUE,FALSE)
which expressed more simply is
=OR(A1="A",B1="A",C1="A")
which would return a value of TRUE if ANY condition was met or FALSE if
ALL were not met.
Again, this could be written as
=((A1="A")+(B1="A")+(C1="A"))0
which resolves to (0 or 1)+(0 or 1)+(0 or 1)
where ANY of those conditions returning 1, will result in the whole
formula returning at least 1 (TRUE) because they are ADDED together
which is the same as OR'ing
In this scenario, the final value to the left of the " operator can be
0,1,2,or 3 where 0 is FALSE and 1, 2 or 3 is TRUE
--
Regards

Roger Govier


"Epinn" wrote in message
...
In order *not* to confuse SUMPRODUCT, we use semicolon as argument
separator when comma is used as digit grouping symbol.

e.g.

=SUMPRODUCT(--(A1:A10=123,45);--(B1:B10="abc");C1:C10)

However, we use the plus sign (+) to indicate both OR and ADDITION.

e.g.

+ used for "addition"

=SUMPRODUCT(--(C2:C31="TLG"),--(H2:H31="TLG"),D2:D31+I2:I31)

+ used for "or"

=SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0))

I guess SUMPRODUCT knows that the "+" means "or" because of the equal
sign (i.e. logical test)? Why not use double plus (++) for "or" test?
Just a thought. Don't mean to confuse anyone.

I find all this interesting.

Epinn

Bob,

I think one of my problems is I learn how to run before I learn how to walk. For example, I studied SUMPRODUCT before I read about SUM. ;)

Sometimes I don't feel that I am the "majority" meaning that I may have a unique way of interpreting things. When it comes to learning, I make a point not to compartmentalize. All this plus being detailed or a _ _ _ contribute to numerous questions. Thank you all for putting up with me.

Yes, I am interested in counting and summing as well and I try to pick out these threads to learn. Whenever there is a SUMPRODUCT thread, I probably read it. Bob, thank you for posting that link on ROUNDING. I am going to study it too.

Always appreciate everyone's help.

Epinn

"Bob Phillips" wrote in message ...
No problem Epinn. Actually I have found these exchanges very useful as I
give teaching sessions on complex counting and summing, and in my ignorance
I never saw things in the same way that you did. I was making some big
assumptions. Hopefully I can incorporate these lessons back in.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob and Roger,

Thanks for straightening me out. Guess I have g _ _ _ _ _ up big time.
Glad I found out at this early stage of learning. It is helpful for me to
see the similar formulae grouped together and explained. I appreciate that.

Yes, it has nothing to do with SUMPRODUCT. When I first learned about
double negating, coercing etc., SUMPRODUCT was the example. So, I thought
it only happened to SUMPRODUCT. Subsequently, when I found out I could use
double negating in VLOOKUP when the data type of the lookup value and the
array didn't match, I was "surprised" and realized that I had the wrong
impression.

So, when we include plus (+) for "or" in a formula, an addition actually
takes place. I think I am okay with *, -- and +. I won't worry about ^ and
N( ).

Thanks again for a valuable lesson and I know this kind of foundation is
important for me down the road.

Cheers,

Epinn

"Roger Govier" wrote in message
...
Hi

It has nothing to do with Sumproduct per se.
It has everything to do with Logic and Boolean operators.

Consider cells A1, B1 and C1.
We could write
=IF(AND(A1="A",B1="A",C1="A"),TRUE,FALSE)
Equally we could write that more simply as
=AND(A1="A",B1="A",C1="A")
which would return a value of TRUE if ALL 3 conditions were met or FALSE
if ANY was not met.
This could be written as
=((A1="A")*(B1="A")*(C1="A")0)
So we would have (TRUE or FALSE)*(TRUE or FALSE)*(TRUE or FALSE)
which becomes (0 or 1)*(0 or 1)*(0 or 1)
where ANY of those conditions returning 0, will result in the whole
formula returning 0 (FALSE) because they are Multiplied together which
is the same as AND'ing them together. It would require 3 * 1's (TRUES )
to give a result of 1 (TRUE).
In this scenario, the final value to the left of the "" operator can
only be 0 or 1.

Alternatively
=IF(OR(A1="A",B1="A",C1="A"),TRUE,FALSE)
which expressed more simply is
=OR(A1="A",B1="A",C1="A")
which would return a value of TRUE if ANY condition was met or FALSE if
ALL were not met.
Again, this could be written as
=((A1="A")+(B1="A")+(C1="A"))0
which resolves to (0 or 1)+(0 or 1)+(0 or 1)
where ANY of those conditions returning 1, will result in the whole
formula returning at least 1 (TRUE) because they are ADDED together
which is the same as OR'ing
In this scenario, the final value to the left of the " operator can be
0,1,2,or 3 where 0 is FALSE and 1, 2 or 3 is TRUE
--
Regards

Roger Govier


"Epinn" wrote in message
...
In order *not* to confuse SUMPRODUCT, we use semicolon as argument
separator when comma is used as digit grouping symbol.

e.g.

=SUMPRODUCT(--(A1:A10=123,45);--(B1:B10="abc");C1:C10)

However, we use the plus sign (+) to indicate both OR and ADDITION.

e.g.

+ used for "addition"

=SUMPRODUCT(--(C2:C31="TLG"),--(H2:H31="TLG"),D2:D31+I2:I31)

+ used for "or"

=SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0))

I guess SUMPRODUCT knows that the "+" means "or" because of the equal
sign (i.e. logical test)? Why not use double plus (++) for "or" test?
Just a thought. Don't mean to confuse anyone.

I find all this interesting.

Epinn