If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMPRODUCT with YEAR, MONTH functions
I am trying to sum data if a date range matches (year and
month only). Here is the example: Table1 5/15/00 5/16/00 6/17/00 (NAME=DATES1) 5 5 5 (NAME=SALES) Table2 5/1/00 6/1/00 7/1/00 (NAME=DATES2) sum1 sum2 sum3 In Table2 I want to create a formula to sum the data by month under the appropriate month (ie. in sum1, sum2 and sum3). Using the named ranges above I created the following formula: =SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR(DATES1) =YEAR(DATES2)),SALES) This formula does not work but the results I would like is to have in Table 2 are - $10 under 5/1/00, $5 under 6/1/00 and $0 under 7/1/00. Unfortunately my formula does not work. Am I on the right track or should I do something else? Thanks in advance. Attila Fust |
#2
|
|||
|
|||
SUMPRODUCT with YEAR, MONTH functions
Assuming Table 2 is in say A20:M20
=SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR(DATES1)=YEAR(A20)),SALES) and then copy across -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Attila Fust" wrote in message ... I am trying to sum data if a date range matches (year and month only). Here is the example: Table1 5/15/00 5/16/00 6/17/00 (NAME=DATES1) 5 5 5 (NAME=SALES) Table2 5/1/00 6/1/00 7/1/00 (NAME=DATES2) sum1 sum2 sum3 In Table2 I want to create a formula to sum the data by month under the appropriate month (ie. in sum1, sum2 and sum3). Using the named ranges above I created the following formula: =SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR(DATES1) =YEAR(DATES2)),SALES) This formula does not work but the results I would like is to have in Table 2 are - $10 under 5/1/00, $5 under 6/1/00 and $0 under 7/1/00. Unfortunately my formula does not work. Am I on the right track or should I do something else? Thanks in advance. Attila Fust |
#3
|
|||
|
|||
SUMPRODUCT with YEAR, MONTH functions
"Bob Phillips" wrote...
... =SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR(DATES1)=YEAR(A20)),SALES) ... Or =SUMIF(DATES1,""&(A20-DAY(A20)),SALES) -SUMIF(DATES1,""&(A20-DAY(A20)+31-DAY(A20-DAY(A20)+31))) or =SUMPRODUCT(--(TEXT(DATES1,"yyyymm")=TEXT(A20,"yyyymm")),SALES) The first, though longer, is likely to be fastest. -- To top-post is human, to bottom-post and snip is sublime. |
#4
|
|||
|
|||
SUMPRODUCT with YEAR, MONTH functions
Thanks, that worked. I am wondering what the "--"
represents in the formula? If I take it out it does not work. Using the following returns 0: =SUMPRODUCT((MONTH(DATES1)=MONTH(A20)),(YEAR(DATE S1)=YEAR (A20)),SALES) I thought the "--" was just to make the formula easier to read. What exactly does it mean? Attila -----Original Message----- Assuming Table 2 is in say A20:M20 =SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR(DATES1) =YEAR(A20)),SALES) and then copy across -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Attila Fust" wrote in message ... I am trying to sum data if a date range matches (year and month only). Here is the example: Table1 5/15/00 5/16/00 6/17/00 (NAME=DATES1) 5 5 5 (NAME=SALES) Table2 5/1/00 6/1/00 7/1/00 (NAME=DATES2) sum1 sum2 sum3 In Table2 I want to create a formula to sum the data by month under the appropriate month (ie. in sum1, sum2 and sum3). Using the named ranges above I created the following formula: =SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR (DATES1) =YEAR(DATES2)),SALES) This formula does not work but the results I would like is to have in Table 2 are - $10 under 5/1/00, $5 under 6/1/00 and $0 under 7/1/00. Unfortunately my formula does not work. Am I on the right track or should I do something else? Thanks in advance. Attila Fust . |
#5
|
|||
|
|||
SUMPRODUCT with YEAR, MONTH functions
The comparisons returns arrays of Boolean values, TRUE and FALSE. The -- is
used to coerce those Booleans to 1/0 integers, which can simply be summed. To see, take a small test range, and select the part of the formula in the formula bar that says MONTH(DATES1)=MONTH(A20). Press F9 and you will see an array of TRUE/FALSE (make the test range small so that F9 can evaluate it in the formula bar). -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Attila Fust" wrote in message ... Thanks, that worked. I am wondering what the "--" represents in the formula? If I take it out it does not work. Using the following returns 0: =SUMPRODUCT((MONTH(DATES1)=MONTH(A20)),(YEAR(DATE S1)=YEAR (A20)),SALES) I thought the "--" was just to make the formula easier to read. What exactly does it mean? Attila -----Original Message----- Assuming Table 2 is in say A20:M20 =SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR(DATES1) =YEAR(A20)),SALES) and then copy across -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Attila Fust" wrote in message ... I am trying to sum data if a date range matches (year and month only). Here is the example: Table1 5/15/00 5/16/00 6/17/00 (NAME=DATES1) 5 5 5 (NAME=SALES) Table2 5/1/00 6/1/00 7/1/00 (NAME=DATES2) sum1 sum2 sum3 In Table2 I want to create a formula to sum the data by month under the appropriate month (ie. in sum1, sum2 and sum3). Using the named ranges above I created the following formula: =SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR (DATES1) =YEAR(DATES2)),SALES) This formula does not work but the results I would like is to have in Table 2 are - $10 under 5/1/00, $5 under 6/1/00 and $0 under 7/1/00. Unfortunately my formula does not work. Am I on the right track or should I do something else? Thanks in advance. Attila Fust . |
#6
|
|||
|
|||
SUMPRODUCT with YEAR, MONTH functions
Your help is much appreciated.
Regards, Attila Fust -----Original Message----- The comparisons returns arrays of Boolean values, TRUE and FALSE. The -- is used to coerce those Booleans to 1/0 integers, which can simply be summed. To see, take a small test range, and select the part of the formula in the formula bar that says MONTH(DATES1)=MONTH(A20). Press F9 and you will see an array of TRUE/FALSE (make the test range small so that F9 can evaluate it in the formula bar). -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Attila Fust" wrote in message ... Thanks, that worked. I am wondering what the "--" represents in the formula? If I take it out it does not work. Using the following returns 0: =SUMPRODUCT((MONTH(DATES1)=MONTH(A20)),(YEAR(DATE S1) =YEAR (A20)),SALES) I thought the "--" was just to make the formula easier to read. What exactly does it mean? Attila -----Original Message----- Assuming Table 2 is in say A20:M20 =SUMPRODUCT(--(MONTH(DATES1)=MONTH(A20)),--(YEAR (DATES1) =YEAR(A20)),SALES) and then copy across -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Attila Fust" wrote in message ... I am trying to sum data if a date range matches (year and month only). Here is the example: Table1 5/15/00 5/16/00 6/17/00 (NAME=DATES1) 5 5 5 (NAME=SALES) Table2 5/1/00 6/1/00 7/1/00 (NAME=DATES2) sum1 sum2 sum3 In Table2 I want to create a formula to sum the data by month under the appropriate month (ie. in sum1, sum2 and sum3). Using the named ranges above I created the following formula: =SUMPRODUCT(--(MONTH(DATES1)=MONTH(DATES2)),--(YEAR (DATES1) =YEAR(DATES2)),SALES) This formula does not work but the results I would like is to have in Table 2 are - $10 under 5/1/00, $5 under 6/1/00 and $0 under 7/1/00. Unfortunately my formula does not work. Am I on the right track or should I do something else? Thanks in advance. Attila Fust . . |
Thread Tools | |
Display Modes | |
|
|