| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF
I have a worksheet that has 6 colums of dates and then 6 colums of numbers.
I am trying to say take the 6 columns of dates if they are < or = to 4/1/2006 add all the numbers in the other six columns. I tried: =SUMIF(F44:K51,"<"&J65,T44:Y51), F44:K51= DATES FROM 4/28/06 THROUGH 1/12/2007 J65=4/1/2006 T44:Y51=NUMBER VALUES. If I understand this correctly, I have two dates in the "Date Range" that are 4/4/06 and 4/28/06 so it should pick up the numerical values 1 and 1 = 2, but I'm not getting that answer. Any and all help will be appreciated Thanks -- DMM |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF
That works fine. Maybe layout the data for us and tell us what you do get. I
assume that J65 holds 1st April. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "soconfused" wrote in message ... I have a worksheet that has 6 colums of dates and then 6 colums of numbers. I am trying to say take the 6 columns of dates if they are < or = to 4/1/2006 add all the numbers in the other six columns. I tried: =SUMIF(F44:K51,"<"&J65,T44:Y51), F44:K51= DATES FROM 4/28/06 THROUGH 1/12/2007 J65=4/1/2006 T44:Y51=NUMBER VALUES. If I understand this correctly, I have two dates in the "Date Range" that are 4/4/06 and 4/28/06 so it should pick up the numerical values 1 and 1 = 2, but I'm not getting that answer. Any and all help will be appreciated Thanks -- DMM |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF
Okay, here's a sampling:
F G H I J K 08/11/06 08/11/06 08/11/06 08/11/06 08/11/06 11/03/06 07/07/06 08/18/06 08/18/06 08/18/06 09/29/06 12/15/06 08/11/06 08/11/06 08/11/06 08/18/06 08/25/06 11/17/06 07/28/06 07/28/06 07/28/06 07/28/06 09/07/06 10/06/06 07/21/06 07/28/06 07/28/06 09/15/06 09/15/06 11/03/06 09/18/06 10/06/06 10/06/06 10/06/06 10/06/06 12/22/06 04/04/06 04/14/06 04/14/06 04/28/06 05/18/06 07/21/06 04/28/06 04/28/06 04/28/06 05/12/06 05/12/06 08/11/06 T U V W X Y 1 0 0 0 64 24 1 1 1 0 90 8 1 1 1 2 176 12 1 1 1 3 218 16 1 1 1 2 108 6 1 1 1 19 926 24 1 1 1 3 100 15 1 1 1 1 200 250 J65 = 4/1/2006 The result I'm getting is 1 -- DMM "Bob Phillips" wrote: That works fine. Maybe layout the data for us and tell us what you do get. I assume that J65 holds 1st April. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "soconfused" wrote in message ... I have a worksheet that has 6 colums of dates and then 6 colums of numbers. I am trying to say take the 6 columns of dates if they are < or = to 4/1/2006 add all the numbers in the other six columns. I tried: =SUMIF(F44:K51,"<"&J65,T44:Y51), F44:K51= DATES FROM 4/28/06 THROUGH 1/12/2007 J65=4/1/2006 T44:Y51=NUMBER VALUES. If I understand this correctly, I have two dates in the "Date Range" that are 4/4/06 and 4/28/06 so it should pick up the numerical values 1 and 1 = 2, but I'm not getting that answer. Any and all help will be appreciated Thanks -- DMM |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF
Try this:
=SUMPRODUCT((F44:K51<J65)*(T44:Y51)) "soconfused" wrote: Okay, here's a sampling: F G H I J K 08/11/06 08/11/06 08/11/06 08/11/06 08/11/06 11/03/06 07/07/06 08/18/06 08/18/06 08/18/06 09/29/06 12/15/06 08/11/06 08/11/06 08/11/06 08/18/06 08/25/06 11/17/06 07/28/06 07/28/06 07/28/06 07/28/06 09/07/06 10/06/06 07/21/06 07/28/06 07/28/06 09/15/06 09/15/06 11/03/06 09/18/06 10/06/06 10/06/06 10/06/06 10/06/06 12/22/06 04/04/06 04/14/06 04/14/06 04/28/06 05/18/06 07/21/06 04/28/06 04/28/06 04/28/06 05/12/06 05/12/06 08/11/06 T U V W X Y 1 0 0 0 64 24 1 1 1 0 90 8 1 1 1 2 176 12 1 1 1 3 218 16 1 1 1 2 108 6 1 1 1 19 926 24 1 1 1 3 100 15 1 1 1 1 200 250 J65 = 4/1/2006 The result I'm getting is 1 -- DMM "Bob Phillips" wrote: That works fine. Maybe layout the data for us and tell us what you do get. I assume that J65 holds 1st April. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "soconfused" wrote in message ... I have a worksheet that has 6 colums of dates and then 6 colums of numbers. I am trying to say take the 6 columns of dates if they are < or = to 4/1/2006 add all the numbers in the other six columns. I tried: =SUMIF(F44:K51,"<"&J65,T44:Y51), F44:K51= DATES FROM 4/28/06 THROUGH 1/12/2007 J65=4/1/2006 T44:Y51=NUMBER VALUES. If I understand this correctly, I have two dates in the "Date Range" that are 4/4/06 and 4/28/06 so it should pick up the numerical values 1 and 1 = 2, but I'm not getting that answer. Any and all help will be appreciated Thanks -- DMM |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF
That worked, except that I need to put the values in each "date bucket". Ex:
1+1+1+1+1 needs to go in the 4/1/2006 date bucket. In other words, I need to grab the values from the T through Y colums to put in individual columns. -- DMM "Teethless mama" wrote: Try this: =SUMPRODUCT((F44:K51<J65)*(T44:Y51)) "soconfused" wrote: Okay, here's a sampling: F G H I J K 08/11/06 08/11/06 08/11/06 08/11/06 08/11/06 11/03/06 07/07/06 08/18/06 08/18/06 08/18/06 09/29/06 12/15/06 08/11/06 08/11/06 08/11/06 08/18/06 08/25/06 11/17/06 07/28/06 07/28/06 07/28/06 07/28/06 09/07/06 10/06/06 07/21/06 07/28/06 07/28/06 09/15/06 09/15/06 11/03/06 09/18/06 10/06/06 10/06/06 10/06/06 10/06/06 12/22/06 04/04/06 04/14/06 04/14/06 04/28/06 05/18/06 07/21/06 04/28/06 04/28/06 04/28/06 05/12/06 05/12/06 08/11/06 T U V W X Y 1 0 0 0 64 24 1 1 1 0 90 8 1 1 1 2 176 12 1 1 1 3 218 16 1 1 1 2 108 6 1 1 1 19 926 24 1 1 1 3 100 15 1 1 1 1 200 250 J65 = 4/1/2006 The result I'm getting is 1 -- DMM "Bob Phillips" wrote: That works fine. Maybe layout the data for us and tell us what you do get. I assume that J65 holds 1st April. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "soconfused" wrote in message ... I have a worksheet that has 6 colums of dates and then 6 colums of numbers. I am trying to say take the 6 columns of dates if they are < or = to 4/1/2006 add all the numbers in the other six columns. I tried: =SUMIF(F44:K51,"<"&J65,T44:Y51), F44:K51= DATES FROM 4/28/06 THROUGH 1/12/2007 J65=4/1/2006 T44:Y51=NUMBER VALUES. If I understand this correctly, I have two dates in the "Date Range" that are 4/4/06 and 4/28/06 so it should pick up the numerical values 1 and 1 = 2, but I'm not getting that answer. Any and all help will be appreciated Thanks -- DMM |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif | Excel Worksheet Functions | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| SUMIF Help... | Excel Discussion (Misc queries) | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
