Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problem
Hi All,
Here's another I hope you can help with. Here's my formula that works fine: {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D $6:$D$417)} Now I want to change the end to ,$D$6:$O$417)}, but changing the D to an O gives the old #VALUE!. I don't suspect the cells in the formula because I'm using them with other similar formulas. Any help would be appreciated. Thanks, Michele |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problem
Try it like this...
Just a normal ENTER will do. No need to array enter. =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. -- Biff Microsoft Excel MVP "mjones" wrote in message ... Hi All, Here's another I hope you can help with. Here's my formula that works fine: {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D $6:$D$417)} Now I want to change the end to ,$D$6:$O$417)}, but changing the D to an O gives the old #VALUE!. I don't suspect the cells in the formula because I'm using them with other similar formulas. Any help would be appreciated. Thanks, Michele |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problem
"T. Valko" wrote:
Try it like this... [....] =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. ..... Which we can avoid by using something like the original form, namely: =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463),$D$6:$O$417) Since the OP was complaining of #VALUE errors, I don't see why you would steer him in a direction that might exacerbate the problem. ----- original message ----- "T. Valko" wrote in message ... Try it like this... Just a normal ENTER will do. No need to array enter. =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. -- Biff Microsoft Excel MVP "mjones" wrote in message ... Hi All, Here's another I hope you can help with. Here's my formula that works fine: {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D $6:$D$417)} Now I want to change the end to ,$D$6:$O$417)}, but changing the D to an O gives the old #VALUE!. I don't suspect the cells in the formula because I'm using them with other similar formulas. Any help would be appreciated. Thanks, Michele |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problem
Errata....
I wrote: "T. Valko" wrote: Try it like this... [....] =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. [....] Since the OP was complaining of #VALUE errors, I don't see why you would steer him in a direction that might exacerbate the problem. My mistake: I see now that your formulation might solve the OP's problem. At least it does address the original reason for the #VALUE error. Mea culpa! I would have explained that the original #VALUE error resulted from the fact that the dimensions of the arrays were incompatible. As the SUMPRODUCT Help page explains: "The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value." It might be helpful to Michele. It certainly would have avoided my mistake. ----- original message ----- "Joe User" <joeu2004 wrote in message ... "T. Valko" wrote: Try it like this... [....] =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. .... Which we can avoid by using something like the original form, namely: =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463),$D$6:$O$417) Since the OP was complaining of #VALUE errors, I don't see why you would steer him in a direction that might exacerbate the problem. ----- original message ----- "T. Valko" wrote in message ... Try it like this... Just a normal ENTER will do. No need to array enter. =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. -- Biff Microsoft Excel MVP "mjones" wrote in message ... Hi All, Here's another I hope you can help with. Here's my formula that works fine: {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D $6:$D$417)} Now I want to change the end to ,$D$6:$O$417)}, but changing the D to an O gives the old #VALUE!. I don't suspect the cells in the formula because I'm using them with other similar formulas. Any help would be appreciated. Thanks, Michele |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problem
Biff would have meant to post the below
=SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463) *ISNUMBER($D$6:$O$417),$D$6:$O$417) If this post helps click Yes --------------- Jacob Skaria "Joe User" wrote: "T. Valko" wrote: Try it like this... [....] =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. ..... Which we can avoid by using something like the original form, namely: =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463),$D$6:$O$417) Since the OP was complaining of #VALUE errors, I don't see why you would steer him in a direction that might exacerbate the problem. ----- original message ----- "T. Valko" wrote in message ... Try it like this... Just a normal ENTER will do. No need to array enter. =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. -- Biff Microsoft Excel MVP "mjones" wrote in message ... Hi All, Here's another I hope you can help with. Here's my formula that works fine: {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D $6:$D$417)} Now I want to change the end to ,$D$6:$O$417)}, but changing the D to an O gives the old #VALUE!. I don't suspect the cells in the formula because I'm using them with other similar formulas. Any help would be appreciated. Thanks, Michele . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problem
On Nov 12, 3:44*am, Jacob Skaria
wrote: Biff would have meant to post the below =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463) *ISNUMBER($D$6:$O$417),$D$6:$O$417) If this post helps click Yes --------------- Jacob Skaria "Joe User" wrote: "T. Valko" wrote: Try it like this... [....] =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. ..... Which we can avoid by using something like the original form, namely: =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463),$D$6:$O$417) Since the OP was complaining of #VALUE errors, I don't see why you would steer him in a direction that might exacerbate the problem. ----- original message ----- "T. Valko" wrote in message ... Try it like this... Just a normal ENTER will do. No need to array enter. =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. -- Biff Microsoft Excel MVP "mjones" wrote in message .... Hi All, Here's another I hope you can help with. Here's my formula that works fine: {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D $6:$D$417)} Now I want to change the end to ,$D$6:$O$417)}, but changing the D to an O gives the old #VALUE!. I don't suspect the cells in the formula because I'm using them with other similar formulas. Any help would be appreciated. Thanks, Michele . Oh my. I'm scaring myself here. The formula works! I'm revamping a crazy spreadsheet to manage my companies finances because the old one just wasn't cutting it. I've added some columns and rows so their different from the last formula. I'm calculating values by quarters so the last formula above was for the first quarter (before October 1st). Now I realize I need to do it again, but between two dates. Here is the old formula: =SUMPRODUCT(--($B$6:$B$484DATE(2009,9,30)),--($B$6:$B$484<DATE (2010,1,1)),--($V$6:$V$484=$B510),$H$6:$H$484) I need to extend the end $H$6:$H$484 to be $H$6:$S$484. Please help because this is Greek to me. Plus I've been up half the night three nights in a row trying to do this and my brain is fried. Although someone explained the double dash thing before, I don't have the background to understand the explanation. Many thanks! I teach project management and always tell my students how great this board is and how you've saved my skin repeatedly! Michele |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problem
Try one of these...
If there is no TEXT in the range $H$6:$S$484 =SUMPRODUCT(($B$6:$B$484DATE(2009,9,30))*($B$6:$B $484<DATE (2010,1,1))*($V$6:$V$484=$B510)*$H$6:$S$484) If there might be TEXT in the range $H$6:$S$484 =SUMPRODUCT(($B$6:$B$484DATE(2009,9,30))*($B$6:$B $484<DATE (2010,1,1))*($V$6:$V$484=$B510)*(ISNUMBER($H$6:$S$ 484)),$H$6:$S$484) -- Biff Microsoft Excel MVP "mjones" wrote in message ... On Nov 12, 3:44 am, Jacob Skaria wrote: Biff would have meant to post the below =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463) *ISNUMBER($D$6:$O$417),$D$6:$O$417) If this post helps click Yes --------------- Jacob Skaria "Joe User" wrote: "T. Valko" wrote: Try it like this... [....] =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. ..... Which we can avoid by using something like the original form, namely: =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463),$D$6:$O$417) Since the OP was complaining of #VALUE errors, I don't see why you would steer him in a direction that might exacerbate the problem. ----- original message ----- "T. Valko" wrote in message ... Try it like this... Just a normal ENTER will do. No need to array enter. =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. -- Biff Microsoft Excel MVP "mjones" wrote in message ... Hi All, Here's another I hope you can help with. Here's my formula that works fine: {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D $6:$D$417)} Now I want to change the end to ,$D$6:$O$417)}, but changing the D to an O gives the old #VALUE!. I don't suspect the cells in the formula because I'm using them with other similar formulas. Any help would be appreciated. Thanks, Michele . Oh my. I'm scaring myself here. The formula works! I'm revamping a crazy spreadsheet to manage my companies finances because the old one just wasn't cutting it. I've added some columns and rows so their different from the last formula. I'm calculating values by quarters so the last formula above was for the first quarter (before October 1st). Now I realize I need to do it again, but between two dates. Here is the old formula: =SUMPRODUCT(--($B$6:$B$484DATE(2009,9,30)),--($B$6:$B$484<DATE (2010,1,1)),--($V$6:$V$484=$B510),$H$6:$H$484) I need to extend the end $H$6:$H$484 to be $H$6:$S$484. Please help because this is Greek to me. Plus I've been up half the night three nights in a row trying to do this and my brain is fried. Although someone explained the double dash thing before, I don't have the background to understand the explanation. Many thanks! I teach project management and always tell my students how great this board is and how you've saved my skin repeatedly! Michele |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problem
On Nov 12, 6:51*pm, "T. Valko" wrote:
Try one of these... If there is no TEXT in the range $H$6:$S$484 =SUMPRODUCT(($B$6:$B$484DATE(2009,9,30))*($B$6:$B $484<DATE (2010,1,1))*($V$6:$V$484=$B510)*$H$6:$S$484) If there might be TEXT in the range $H$6:$S$484 =SUMPRODUCT(($B$6:$B$484DATE(2009,9,30))*($B$6:$B $484<DATE (2010,1,1))*($V$6:$V$484=$B510)*(ISNUMBER($H$6:$S$ 484)),$H$6:$S$484) -- Biff Microsoft Excel MVP "mjones" wrote in message ... On Nov 12, 3:44 am, Jacob Skaria wrote: Biff would have meant to post the below =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463) *ISNUMBER($D$6:$O$417),$D$6:$O$417) If this post helps click Yes --------------- Jacob Skaria "Joe User" wrote: "T. Valko" wrote: Try it like this... [....] =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. ..... Which we can avoid by using something like the original form, namely: =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463),$D$6:$O$417) Since the OP was complaining of #VALUE errors, I don't see why you would steer him in a direction that might exacerbate the problem. ----- original message ----- "T. Valko" wrote in message ... Try it like this... Just a normal ENTER will do. No need to array enter. =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. -- Biff Microsoft Excel MVP "mjones" wrote in message ... Hi All, Here's another I hope you can help with. Here's my formula that works fine: {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D $6:$D$417)} Now I want to change the end to ,$D$6:$O$417)}, but changing the D to an O gives the old #VALUE!. I don't suspect the cells in the formula because I'm using them with other similar formulas. Any help would be appreciated. Thanks, Michele . Oh my. *I'm scaring myself here. *The formula works! I'm revamping a crazy spreadsheet to manage my companies finances because the old one just wasn't cutting it. *I've added some columns and rows so their different from the last formula. *I'm calculating values by quarters so the last formula above was for the first quarter (before October 1st). *Now I realize I need to do it again, but between two dates. Here is the old formula: =SUMPRODUCT(--($B$6:$B$484DATE(2009,9,30)),--($B$6:$B$484<DATE (2010,1,1)),--($V$6:$V$484=$B510),$H$6:$H$484) I need to extend the end $H$6:$H$484 to be $H$6:$S$484. *Please help because this is Greek to me. *Plus I've been up half the night three nights in a row trying to do this and my brain is fried. *Although someone explained the double dash thing before, I don't have the background to understand the explanation. Many thanks! *I teach project management and always tell my students how great this board is and how you've saved my skin repeatedly! Michele Yep, that did it Biff. I used the last one even though there wasn't any text. Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula problem
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "mjones" wrote in message ... On Nov 12, 6:51 pm, "T. Valko" wrote: Try one of these... If there is no TEXT in the range $H$6:$S$484 =SUMPRODUCT(($B$6:$B$484DATE(2009,9,30))*($B$6:$B $484<DATE (2010,1,1))*($V$6:$V$484=$B510)*$H$6:$S$484) If there might be TEXT in the range $H$6:$S$484 =SUMPRODUCT(($B$6:$B$484DATE(2009,9,30))*($B$6:$B $484<DATE (2010,1,1))*($V$6:$V$484=$B510)*(ISNUMBER($H$6:$S$ 484)),$H$6:$S$484) -- Biff Microsoft Excel MVP "mjones" wrote in message ... On Nov 12, 3:44 am, Jacob Skaria wrote: Biff would have meant to post the below =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463) *ISNUMBER($D$6:$O$417),$D$6:$O$417) If this post helps click Yes --------------- Jacob Skaria "Joe User" wrote: "T. Valko" wrote: Try it like this... [....] =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. ..... Which we can avoid by using something like the original form, namely: =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463),$D$6:$O$417) Since the OP was complaining of #VALUE errors, I don't see why you would steer him in a direction that might exacerbate the problem. ----- original message ----- "T. Valko" wrote in message ... Try it like this... Just a normal ENTER will do. No need to array enter. =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q $417=B463)*$D$6:$O$417) Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error. -- Biff Microsoft Excel MVP "mjones" wrote in message ... Hi All, Here's another I hope you can help with. Here's my formula that works fine: {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D $6:$D$417)} Now I want to change the end to ,$D$6:$O$417)}, but changing the D to an O gives the old #VALUE!. I don't suspect the cells in the formula because I'm using them with other similar formulas. Any help would be appreciated. Thanks, Michele . Oh my. I'm scaring myself here. The formula works! I'm revamping a crazy spreadsheet to manage my companies finances because the old one just wasn't cutting it. I've added some columns and rows so their different from the last formula. I'm calculating values by quarters so the last formula above was for the first quarter (before October 1st). Now I realize I need to do it again, but between two dates. Here is the old formula: =SUMPRODUCT(--($B$6:$B$484DATE(2009,9,30)),--($B$6:$B$484<DATE (2010,1,1)),--($V$6:$V$484=$B510),$H$6:$H$484) I need to extend the end $H$6:$H$484 to be $H$6:$S$484. Please help because this is Greek to me. Plus I've been up half the night three nights in a row trying to do this and my brain is fried. Although someone explained the double dash thing before, I don't have the background to understand the explanation. Many thanks! I teach project management and always tell my students how great this board is and how you've saved my skin repeatedly! Michele Yep, that did it Biff. I used the last one even though there wasn't any text. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem | Excel Worksheet Functions | |||
Problem with this formula | Excel Worksheet Functions | |||
Formula problem | Excel Worksheet Functions | |||
Formula problem | Excel Worksheet Functions | |||
Formula problem | Excel Worksheet Functions |