SUM WITH MULTIPLE CRITERIA
 

I am trying to add a series of numbers using a Date Range formula, however, I
am trying to add a criteria that would look at another column as well. For
example, assume the following:

Date Probability Amount
01/08 90% $1,000,000
01/08 100% $2,000,000
02/08 75% $1,250,000
02/08 100% $2,500,000

I want to sum by month and probability. I have the following date formula

=SUMPRODUCT((MONTH($N$2:$N$30)=MONTH($B$33))*(YEAR ($N$2:$N$30)=YEAR($B$33))*(C2:C30))

How do I add a qualifier to this formula to have it look at the probability
as well - for instance add all values where the probability is less than
100%,

I appreciate your assistance.

SUM WITH MULTIPLE CRITERIA
 

Say "Probability" is in Column B, and you enter the criteria into B32:

=SUMPRODUCT((TEXT(N2:N30,"mm/yy")=TEXT(B33,"mm/yy"))*(B2:B30<B32)*C2:C30)

I combined the date criteria into a single function.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"readystate" wrote in message
...
I am trying to add a series of numbers using a Date Range formula,
however, I
am trying to add a criteria that would look at another column as well.
For
example, assume the following:

Date Probability Amount
01/08 90% $1,000,000
01/08 100% $2,000,000
02/08 75% $1,250,000
02/08 100% $2,500,000

I want to sum by month and probability. I have the following date formula


=SUMPRODUCT((MONTH($N$2:$N$30)=MONTH($B$33))*(YEAR ($N$2:$N$30)=YEAR($B$33))*
(C2:C30))

How do I add a qualifier to this formula to have it look at the
probability
as well - for instance add all values where the probability is less than
100%,

I appreciate your assistance.

SUM WITH MULTIPLE CRITERIA
 

Thanks...worked fine. Appreciate your assistance.

"Ragdyer" wrote:

Say "Probability" is in Column B, and you enter the criteria into B32:

=SUMPRODUCT((TEXT(N2:N30,"mm/yy")=TEXT(B33,"mm/yy"))*(B2:B30<B32)*C2:C30)

I combined the date criteria into a single function.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"readystate" wrote in message
...
I am trying to add a series of numbers using a Date Range formula,
however, I
am trying to add a criteria that would look at another column as well.
For
example, assume the following:

Date Probability Amount
01/08 90% $1,000,000
01/08 100% $2,000,000
02/08 75% $1,250,000
02/08 100% $2,500,000

I want to sum by month and probability. I have the following date formula


=SUMPRODUCT((MONTH($N$2:$N$30)=MONTH($B$33))*(YEAR ($N$2:$N$30)=YEAR($B$33))*
(C2:C30))

How do I add a qualifier to this formula to have it look at the
probability
as well - for instance add all values where the probability is less than
100%,

I appreciate your assistance.

SUM WITH MULTIPLE CRITERIA
 

And I appreciate your feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"readystate" wrote in message
...
Thanks...worked fine. Appreciate your assistance.

"Ragdyer" wrote:

Say "Probability" is in Column B, and you enter the criteria into B32:

=SUMPRODUCT((TEXT(N2:N30,"mm/yy")=TEXT(B33,"mm/yy"))*(B2:B30<B32)*C2:C30)

I combined the date criteria into a single function.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"readystate" wrote in message
...
I am trying to add a series of numbers using a Date Range formula,
however, I
am trying to add a criteria that would look at another column as well.
For
example, assume the following:

Date Probability Amount
01/08 90% $1,000,000
01/08 100% $2,000,000
02/08 75% $1,250,000
02/08 100% $2,500,000

I want to sum by month and probability. I have the following date
formula


=SUMPRODUCT((MONTH($N$2:$N$30)=MONTH($B$33))*(YEAR ($N$2:$N$30)=YEAR($B$33))*
(C2:C30))

How do I add a qualifier to this formula to have it look at the
probability
as well - for instance add all values where the probability is less
than
100%,

I appreciate your assistance.