| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
I've got a fairly complicated sumproduct and I can't seem to get anything but
a value error. This is what I have =SUMPRODUCT(--('2009'!Region_All=$A31),--(IF(ISNUMBER('2009'!Due_Date),--('2009'!Due_DateF$3)))) The named ranges are dynamic ranges I've got the region part working. What I want to find is data in the range Due_Date that is than F3 and < F3 + 2 months. I just can't figure it out. Also, in Due_Date, I've got numerics and N/A. Can Someone assist? Thanks, Barb Reinhardt |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct
in Due_Date I've got numerics and N/A.
Assuming the N/A is a TEXT string and not the error #N/A. Use another cell to calculate < F3+2 months: =EDATE(F3,2) EDATE requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. If you get a #NAME? error see Excel help on the EDATE function and it'll tell you how to correct it. Assume that formula is in G3. Then: =SUMPRODUCT(--('2009'!Region_All=$A31),--('2009'!Due_DateF$3),--('2009'!Due_Date<G$3)) -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... I've got a fairly complicated sumproduct and I can't seem to get anything but a value error. This is what I have =SUMPRODUCT(--('2009'!Region_All=$A31),--(IF(ISNUMBER('2009'!Due_Date),--('2009'!Due_DateF$3)))) The named ranges are dynamic ranges I've got the region part working. What I want to find is data in the range Due_Date that is than F3 and < F3 + 2 months. I just can't figure it out. Also, in Due_Date, I've got numerics and N/A. Can Someone assist? Thanks, Barb Reinhardt |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct
'Assuming the N/A is a TEXT string and not the error #N/A.
I wish we could assume this, but I've got the error #N/A in those cells. "T. Valko" wrote: in Due_Date I've got numerics and N/A. Assuming the N/A is a TEXT string and not the error #N/A. Use another cell to calculate < F3+2 months: =EDATE(F3,2) EDATE requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. If you get a #NAME? error see Excel help on the EDATE function and it'll tell you how to correct it. Assume that formula is in G3. Then: =SUMPRODUCT(--('2009'!Region_All=$A31),--('2009'!Due_DateF$3),--('2009'!Due_Date<G$3)) -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... I've got a fairly complicated sumproduct and I can't seem to get anything but a value error. This is what I have =SUMPRODUCT(--('2009'!Region_All=$A31),--(IF(ISNUMBER('2009'!Due_Date),--('2009'!Due_DateF$3)))) The named ranges are dynamic ranges I've got the region part working. What I want to find is data in the range Due_Date that is than F3 and < F3 + 2 months. I just can't figure it out. Also, in Due_Date, I've got numerics and N/A. Can Someone assist? Thanks, Barb Reinhardt |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct
Never mind. I got rid of the N/A#'s in my source data.
"T. Valko" wrote: in Due_Date I've got numerics and N/A. Assuming the N/A is a TEXT string and not the error #N/A. Use another cell to calculate < F3+2 months: =EDATE(F3,2) EDATE requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. If you get a #NAME? error see Excel help on the EDATE function and it'll tell you how to correct it. Assume that formula is in G3. Then: =SUMPRODUCT(--('2009'!Region_All=$A31),--('2009'!Due_DateF$3),--('2009'!Due_Date<G$3)) -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... I've got a fairly complicated sumproduct and I can't seem to get anything but a value error. This is what I have =SUMPRODUCT(--('2009'!Region_All=$A31),--(IF(ISNUMBER('2009'!Due_Date),--('2009'!Due_DateF$3)))) The named ranges are dynamic ranges I've got the region part working. What I want to find is data in the range Due_Date that is than F3 and < F3 + 2 months. I just can't figure it out. Also, in Due_Date, I've got numerics and N/A. Can Someone assist? Thanks, Barb Reinhardt |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct
Ok, try this array formula** :
=SUM(IF('2009'!Region_All=$A31,IF(ISNUMBER('2009'! Due_Date),('2009'Due_DateF$3)*('2009'!Due_Date<G$ 3)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... 'Assuming the N/A is a TEXT string and not the error #N/A. I wish we could assume this, but I've got the error #N/A in those cells. "T. Valko" wrote: in Due_Date I've got numerics and N/A. Assuming the N/A is a TEXT string and not the error #N/A. Use another cell to calculate < F3+2 months: =EDATE(F3,2) EDATE requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. If you get a #NAME? error see Excel help on the EDATE function and it'll tell you how to correct it. Assume that formula is in G3. Then: =SUMPRODUCT(--('2009'!Region_All=$A31),--('2009'!Due_DateF$3),--('2009'!Due_Date<G$3)) -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... I've got a fairly complicated sumproduct and I can't seem to get anything but a value error. This is what I have =SUMPRODUCT(--('2009'!Region_All=$A31),--(IF(ISNUMBER('2009'!Due_Date),--('2009'!Due_DateF$3)))) The named ranges are dynamic ranges I've got the region part working. What I want to find is data in the range Due_Date that is than F3 and < F3 + 2 months. I just can't figure it out. Also, in Due_Date, I've got numerics and N/A. Can Someone assist? Thanks, Barb Reinhardt |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct
I think I may remember the issue. The dynamic ranges are defined with OFFSET
and I have a feeling I can't use array formulas with dynamic ranges defined this way. Thanks for your help. "T. Valko" wrote: Ok, try this array formula** : =SUM(IF('2009'!Region_All=$A31,IF(ISNUMBER('2009'! Due_Date),('2009'Due_DateF$3)*('2009'!Due_Date<G$ 3)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... 'Assuming the N/A is a TEXT string and not the error #N/A. I wish we could assume this, but I've got the error #N/A in those cells. "T. Valko" wrote: in Due_Date I've got numerics and N/A. Assuming the N/A is a TEXT string and not the error #N/A. Use another cell to calculate < F3+2 months: =EDATE(F3,2) EDATE requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. If you get a #NAME? error see Excel help on the EDATE function and it'll tell you how to correct it. Assume that formula is in G3. Then: =SUMPRODUCT(--('2009'!Region_All=$A31),--('2009'!Due_DateF$3),--('2009'!Due_Date<G$3)) -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... I've got a fairly complicated sumproduct and I can't seem to get anything but a value error. This is what I have =SUMPRODUCT(--('2009'!Region_All=$A31),--(IF(ISNUMBER('2009'!Due_Date),--('2009'!Due_DateF$3)))) The named ranges are dynamic ranges I've got the region part working. What I want to find is data in the range Due_Date that is than F3 and < F3 + 2 months. I just can't figure it out. Also, in Due_Date, I've got numerics and N/A. Can Someone assist? Thanks, Barb Reinhardt |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct
I have a feeling I can't use array formulas with
dynamic ranges defined this way. You can as long as each dynamic range is properly defined to be the same size. For example, Region_All must be the same size as Due_Date. I see in your other reply you got rid of the #N/A errors. That is usually the best option but sometimes you might want the errors for whatever reason and in these cases it's good to have/know a way to account for those in formulas. -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... I think I may remember the issue. The dynamic ranges are defined with OFFSET and I have a feeling I can't use array formulas with dynamic ranges defined this way. Thanks for your help. "T. Valko" wrote: Ok, try this array formula** : =SUM(IF('2009'!Region_All=$A31,IF(ISNUMBER('2009'! Due_Date),('2009'Due_DateF$3)*('2009'!Due_Date<G$ 3)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... 'Assuming the N/A is a TEXT string and not the error #N/A. I wish we could assume this, but I've got the error #N/A in those cells. "T. Valko" wrote: in Due_Date I've got numerics and N/A. Assuming the N/A is a TEXT string and not the error #N/A. Use another cell to calculate < F3+2 months: =EDATE(F3,2) EDATE requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. If you get a #NAME? error see Excel help on the EDATE function and it'll tell you how to correct it. Assume that formula is in G3. Then: =SUMPRODUCT(--('2009'!Region_All=$A31),--('2009'!Due_DateF$3),--('2009'!Due_Date<G$3)) -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... I've got a fairly complicated sumproduct and I can't seem to get anything but a value error. This is what I have =SUMPRODUCT(--('2009'!Region_All=$A31),--(IF(ISNUMBER('2009'!Due_Date),--('2009'!Due_DateF$3)))) The named ranges are dynamic ranges I've got the region part working. What I want to find is data in the range Due_Date that is than F3 and < F3 + 2 months. I just can't figure it out. Also, in Due_Date, I've got numerics and N/A. Can Someone assist? Thanks, Barb Reinhardt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| SumProduct | Excel Discussion (Misc queries) | |||
| SUMPRODUCT help | Excel Worksheet Functions | |||
Linear Mode
