Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |