Thank you to who ever suggested this site
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
But I am still unclear on which unary or operator to use, ,--, *, +, -

I understand by using an arithmetic operator you will get the result of
additions or subtractions.

Here is what I'm up against.

I want to count the number of N's and U's in (column AH), that fall in the
month of July (Column AX) and contains a number in (column AM)

=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999="N")+(Odyssey!$AH$2:$AH$999 ="U"),--
(Odyssey!$AM$2:$AM$999<"")*(Odyssey!$AX$2:$AX$999 =DATE(2006,7,1))*(Odyssey!
$AX$2:$AX$999<=DATE(2006,7,31)))

I am getting the correct results I just want to understand why I used *
between the dates and + between N and U and -- and the start of the formula.

Thanks again

--
Message posted via http://www.officekb.com

s2m wrote:
Thank you to who ever suggested this site
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
But I am still unclear on which unary or operator to use, ,--, *, +,
-

I understand by using an arithmetic operator you will get the result
of additions or subtractions.

Here is what I'm up against.

I want to count the number of N's and U's in (column AH), that fall
in the month of July (Column AX) and contains a number in (column AM)

=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999="N")+(Odyssey!$AH$2:$AH$999 ="U"),--
(Odyssey!$AM$2:$AM$999<"")*(Odyssey!$AX$2:$AX$999 =DATE(2006,7,1))*(Odyssey!
$AX$2:$AX$999<=DATE(2006,7,31)))

I am getting the correct results I just want to understand why I used
* between the dates and + between N and U and -- and the start of the
formula.


For the double - you can find a clear explanation he

http://mcgimpsey.com/excel/formulae/doubleneg.html

while * is the equivalent of the AND operator and + is the equivalent of the
OR operator. So your formula could work also in this way:

=SUMPRODUCT(((Odyssey!$AH$2:$AH$999="N")+(Odyssey! $AH$2:$AH$999="U"))*(Odyssey!$AM$2:$AM$999<"")*(O dyssey!$AX$2:$AX$999=DATE(2006,7,1))*(Odyssey!$AX $2:$AX$999<=DATE(2006,7,31)))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Who knows why you used it? I certainly don't (well actually I do, but it is
not necessary (see later).

I quote from that article, ...

There is no situation that I know of whereby a solution using -- could not
be achieved somehow with a '*'. Conversely, if using the TRANSPOSE function
within SUMPRODUCT, then the '*' has to be used.

In your formula, -- would have worked where you have *,. As written, * would
not have worked where you use --, because of the OR (+) condition, but just
by wrapping extra brackets around the OR conditions, you could have used *.
IMO, the worst you can do is mix them.

You could even get rid of the + with

=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999={"N","U"}),
--(Odyssey!$AM$2:$AM$999<""),
--(Odyssey!$AX$2:$AX$999=DATE(2006,7,1))*(Odyssey!$ AX$2:$AX$999<=DATE(2006,
7,31)))




--
HTH

Bob Phillips

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

"s2m" <u23063@uwe wrote in message news:65346e330a7fa@uwe...
Thank you to who ever suggested this site
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
But I am still unclear on which unary or operator to use, ,--, *, +, -

I understand by using an arithmetic operator you will get the result of
additions or subtractions.

Here is what I'm up against.

I want to count the number of N's and U's in (column AH), that fall in the
month of July (Column AX) and contains a number in (column AM)

=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999="N")+(Odyssey!$AH$2:$AH$999 ="U"),--

(Odyssey!$AM$2:$AM$999<"")*(Odyssey!$AX$2:$AX$999 =DATE(2006,7,1))*(Odyssey
!
$AX$2:$AX$999<=DATE(2006,7,31)))

I am getting the correct results I just want to understand why I used *
between the dates and + between N and U and -- and the start of the
formula.

Thanks again

--
Message posted via http://www.officekb.com

Excel is very forgiving.

I wouldn't have mixed and matched. I would have used separate arguments.

=SUMPRODUCT(--((Odyssey!$AH$2:$AH$999="N")+(Odyssey!$AH$2:$AH$99 9="U")),
--(Odyssey!$AM$2:$AM$999<""),
--(Odyssey!$AX$2:$AX$999=DATE(2006,7,1)),
--(Odyssey!$AX$2:$AX$999<=DATE(2006,7,31)))

The + in the first line of that expression is acting like an "or". If AH#=N or
AH#=U.

But the * in the original formula are acting like "and"'s.

Using the * or using separate arguments pretty much boils down to what you
like. I like the separate arguments. I think it's easier to read/modify.



s2m wrote:

Thank you to who ever suggested this site
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
But I am still unclear on which unary or operator to use, ,--, *, +, -

I understand by using an arithmetic operator you will get the result of
additions or subtractions.

Here is what I'm up against.

I want to count the number of N's and U's in (column AH), that fall in the
month of July (Column AX) and contains a number in (column AM)

=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999="N")+(Odyssey!$AH$2:$AH$999 ="U"),--
(Odyssey!$AM$2:$AM$999<"")*(Odyssey!$AX$2:$AX$999 =DATE(2006,7,1))*(Odyssey!
$AX$2:$AX$999<=DATE(2006,7,31)))

I am getting the correct results I just want to understand why I used *
between the dates and + between N and U and -- and the start of the formula.

Thanks again

--
Message posted via http://www.officekb.com

--

Dave Peterson

=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999={"N","U"}),

is the same as a or statement?




Bob Phillips wrote:
Who knows why you used it? I certainly don't (well actually I do, but it is
not necessary (see later).

I quote from that article, ...

There is no situation that I know of whereby a solution using -- could not
be achieved somehow with a '*'. Conversely, if using the TRANSPOSE function
within SUMPRODUCT, then the '*' has to be used.

In your formula, -- would have worked where you have *,. As written, * would
not have worked where you use --, because of the OR (+) condition, but just
by wrapping extra brackets around the OR conditions, you could have used *.
IMO, the worst you can do is mix them.

You could even get rid of the + with

=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999={"N","U"}),
--(Odyssey!$AM$2:$AM$999<""),
--(Odyssey!$AX$2:$AX$999=DATE(2006,7,1))*(Odyssey!$ AX$2:$AX$999<=DATE(2006,
7,31)))

Thank you to who ever suggested this site
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
[quoted text clipped - 9 lines]

=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999="N")+(Odyssey!$AH$2:$AH$999 ="U"),--

(Odyssey!$AM$2:$AM$999<"")*(Odyssey!$AX$2:$AX$99 9=DATE(2006,7,1))*(Odyssey
!
$AX$2:$AX$999<=DATE(2006,7,31)))

I am getting the correct results I just want to understand why I used *
between the dates and + between N and U and -- and the start of the formula.

Thanks again

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Yes it is, it is saying if the range is equal to N or equal to U. Of course
if you are ORing different ranges, you need the other approach.

--
HTH

Bob Phillips

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

"s2m via OfficeKB.com" <u23063@uwe wrote in message
news:6542e792abe6d@uwe...
=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999={"N","U"}),

is the same as a or statement?




Bob Phillips wrote:
Who knows why you used it? I certainly don't (well actually I do, but it
is
not necessary (see later).

I quote from that article, ...

There is no situation that I know of whereby a solution using -- could
not
be achieved somehow with a '*'. Conversely, if using the TRANSPOSE
function
within SUMPRODUCT, then the '*' has to be used.

In your formula, -- would have worked where you have *,. As written, *
would
not have worked where you use --, because of the OR (+) condition, but
just
by wrapping extra brackets around the OR conditions, you could have used
*.
IMO, the worst you can do is mix them.

You could even get rid of the + with

=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999={"N","U"}),
--(Odyssey!$AM$2:$AM$999<""),

--(Odyssey!$AX$2:$AX$999=DATE(2006,7,1))*(Odyssey!$ AX$2:$AX$999<=DATE(2006
,
7,31)))

Thank you to who ever suggested this site
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
[quoted text clipped - 9 lines]


=SUMPRODUCT(--(Odyssey!$AH$2:$AH$999="N")+(Odyssey!$AH$2:$AH$999 ="U"),--


(Odyssey!$AM$2:$AM$999<"")*(Odyssey!$AX$2:$AX$99 9=DATE(2006,7,1))*(Odysse
y
!
$AX$2:$AX$999<=DATE(2006,7,31)))

I am getting the correct results I just want to understand why I used *
between the dates and + between N and U and -- and the start of the
formula.

Thanks again

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1