|
SUMPRODUCT trouble
Greetings all. I am very new to worksheet formulas and I am having trouble
getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006".
Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Hello Greg, if you only have the formula in one cell then you won't need the
$ signs. These "fix" the cell references so that if you fill the formula down or across they don't change. As written your formula will multiply H33 by N33 by AF33 by AR33 when D31 and E31 meet your criteria and so on down the rows, i.e. you have an "offset" of two rows. Is that what you require? If you want to add the values in those columns when the criteria is met for the same row perhaps =SUMPRODUCT(--(D31:D10000="Jan"),--(E31:E10000=2006),H31:H10000+N31:N10000+AF31:AF100 00+AR31:AR10000) "Greg Snidow" wrote: Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Wonder why. <<
I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Thank you so much for your time and input. I maybe should have included in
my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
I thank you for your post which led me to various experiments. As a result, I have discovered a few things about SUMPRODUCT. I am no expert in Excel but I can share with you what I have learned in the past few days about SUMPRODUCT.
SUMPRODUCT is not as "forgiving" as SUMIF or COUNTIF. I totally understand why SUMIF works for you but not SUMPRODUCT. SP is very disciplined about data type. You must compare text to text and numeric values to numeric values. If your data is text then you must test for text in your formula. The following thread has lots of good info on SUMPRODUCT and data mismatch and how to make the formulae work by coercion etc. Look for Harlan's and Roger's posts. I also posted my experiments there. http://groups.google.ca/group/micros...4ad71c9e0ba655 or http://tinyurl.com/yf6a9v This is an excerpt from one of Harlan's post. "--x is guaranteed to convert text representations of numbers to their numeric values (with possible floating point rounding/truncation error) while leaving numeric values unchanged, and x&"" is guaranteed to convert numeric values to their text representations while leaving text as-is." Bottom line is you have to have data type matching otherwise your SP won't work. I didn't mean that by shrinking your data set, the formula would work. The only reason I suggested you use a sample data set was for you to use the tool "evaluate formula" to debug. It should give you some hint where the problem lies. Have you tried that? I would use about 10 records; 200 may be too much. If you still can't solve your problem after reading the thread, feel free to post back. Hopefully, an Excel guru can provide you with more specific guidance. This is a good link to learn about SUMPRODUCT. http://www.xldynamic.com/source/xld.SUMPRODUCT.html I don't feel comfortable using a function unless I understand it. Good reading! Epinn "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Hi Greg
Try =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Thanks Roger for also giving your input. I see from the other post that it
can get heated. In any event I tried 1*myrange, and I still get a '0'. I have tried formating both the cell with the formula and the range to text and then general, but still '0'. I have tried deleting the contents then formating, then refreshing my data as EPINN suggested, bit still wont work. Is there maybe something to do with importing from a stored procedure? Since sumif works, is there a way to use sumif with two conditions in two different columns? Thank you for all your help. "Roger Govier" wrote: Hi Greg Try =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Hi Greg
Although you are saying that the column D contains "Jan", is it text "Jan" or could it be 01/01/2006 formatted as mmm? What are the lengths returned by the cells in column D, are they all 3 ? =LEN(D2) Try breaking the formula down into pieces, and see if any parts return the correct values e.g. =SUMPRODUCT(--($D$31:$D$10000&""="Jan")) =SUMPRODUCT(--($E$31:$E$10000&""="2006")) Your comparative ranges are not the same as the data ranges to be summed. They are of equal dimension, so Sumproduct will not object, but is that what you intended? If you want to mail me a copy of the workbook, I will take a look. The previous problem was operator error. To mail direct, remove NOSPAM from my address. -- Regards Roger Govier "Greg Snidow" wrote in message ... Thanks Roger for also giving your input. I see from the other post that it can get heated. In any event I tried 1*myrange, and I still get a '0'. I have tried formating both the cell with the formula and the range to text and then general, but still '0'. I have tried deleting the contents then formating, then refreshing my data as EPINN suggested, bit still wont work. Is there maybe something to do with importing from a stored procedure? Since sumif works, is there a way to use sumif with two conditions in two different columns? Thank you for all your help. "Roger Govier" wrote: Hi Greg Try =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Roger. When I tested the formula for only "Jan" it returned the correct
value. When I tested it for only "2007" it returned the correct value after I removed the "" from 2007. But when I put them both together they do not work, so I am thinking I have a syntax error. When I did this test I made a new workbook and did not touch the format of anything. It seems all are general. This is very perplexing to me, so I thank all of you people very much who take the time to help foundlings like me. "Roger Govier" wrote: Hi Greg Although you are saying that the column D contains "Jan", is it text "Jan" or could it be 01/01/2006 formatted as mmm? What are the lengths returned by the cells in column D, are they all 3 ? =LEN(D2) Try breaking the formula down into pieces, and see if any parts return the correct values e.g. =SUMPRODUCT(--($D$31:$D$10000&""="Jan")) =SUMPRODUCT(--($E$31:$E$10000&""="2006")) Your comparative ranges are not the same as the data ranges to be summed. They are of equal dimension, so Sumproduct will not object, but is that what you intended? If you want to mail me a copy of the workbook, I will take a look. The previous problem was operator error. To mail direct, remove NOSPAM from my address. -- Regards Roger Govier "Greg Snidow" wrote in message ... Thanks Roger for also giving your input. I see from the other post that it can get heated. In any event I tried 1*myrange, and I still get a '0'. I have tried formating both the cell with the formula and the range to text and then general, but still '0'. I have tried deleting the contents then formating, then refreshing my data as EPINN suggested, bit still wont work. Is there maybe something to do with importing from a stored procedure? Since sumif works, is there a way to use sumif with two conditions in two different columns? Thank you for all your help. "Roger Govier" wrote: Hi Greg Try =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Greg,
I know it can be frustrating when one has a problem. But you have a good attitude and people are more willing to help. Now that you have Roger's attention, you are closer to solving your problem. Roger is one of my good teachers. Hang in there. We all start from somewhere and I am not experienced in Excel either. I appreciate it if you could post your solution so that we can all learn. Now, I am going to the heated thread and post a "thank you" note to Harlan. Epinn "Greg Snidow" wrote in message ... Roger. When I tested the formula for only "Jan" it returned the correct value. When I tested it for only "2007" it returned the correct value after I removed the "" from 2007. But when I put them both together they do not work, so I am thinking I have a syntax error. When I did this test I made a new workbook and did not touch the format of anything. It seems all are general. This is very perplexing to me, so I thank all of you people very much who take the time to help foundlings like me. "Roger Govier" wrote: Hi Greg Although you are saying that the column D contains "Jan", is it text "Jan" or could it be 01/01/2006 formatted as mmm? What are the lengths returned by the cells in column D, are they all 3 ? =LEN(D2) Try breaking the formula down into pieces, and see if any parts return the correct values e.g. =SUMPRODUCT(--($D$31:$D$10000&""="Jan")) =SUMPRODUCT(--($E$31:$E$10000&""="2006")) Your comparative ranges are not the same as the data ranges to be summed. They are of equal dimension, so Sumproduct will not object, but is that what you intended? If you want to mail me a copy of the workbook, I will take a look. The previous problem was operator error. To mail direct, remove NOSPAM from my address. -- Regards Roger Govier "Greg Snidow" wrote in message ... Thanks Roger for also giving your input. I see from the other post that it can get heated. In any event I tried 1*myrange, and I still get a '0'. I have tried formating both the cell with the formula and the range to text and then general, but still '0'. I have tried deleting the contents then formating, then refreshing my data as EPINN suggested, bit still wont work. Is there maybe something to do with importing from a stored procedure? Since sumif works, is there a way to use sumif with two conditions in two different columns? Thank you for all your help. "Roger Govier" wrote: Hi Greg Try =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
I am very curious if changing the comma (,) between the "Jan" argument and "2006" argument to * will make a difference. I always use "*" to check for multiple conditions.
=SUMPRODUCT(--($D$31:$D$10000&""="Jan")* --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) Epinn =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) "Greg Snidow" wrote in message ... Roger. When I tested the formula for only "Jan" it returned the correct value. When I tested it for only "2007" it returned the correct value after I removed the "" from 2007. But when I put them both together they do not work, so I am thinking I have a syntax error. When I did this test I made a new workbook and did not touch the format of anything. It seems all are general. This is very perplexing to me, so I thank all of you people very much who take the time to help foundlings like me. "Roger Govier" wrote: Hi Greg Although you are saying that the column D contains "Jan", is it text "Jan" or could it be 01/01/2006 formatted as mmm? What are the lengths returned by the cells in column D, are they all 3 ? =LEN(D2) Try breaking the formula down into pieces, and see if any parts return the correct values e.g. =SUMPRODUCT(--($D$31:$D$10000&""="Jan")) =SUMPRODUCT(--($E$31:$E$10000&""="2006")) Your comparative ranges are not the same as the data ranges to be summed. They are of equal dimension, so Sumproduct will not object, but is that what you intended? If you want to mail me a copy of the workbook, I will take a look. The previous problem was operator error. To mail direct, remove NOSPAM from my address. -- Regards Roger Govier "Greg Snidow" wrote in message ... Thanks Roger for also giving your input. I see from the other post that it can get heated. In any event I tried 1*myrange, and I still get a '0'. I have tried formating both the cell with the formula and the range to text and then general, but still '0'. I have tried deleting the contents then formating, then refreshing my data as EPINN suggested, bit still wont work. Is there maybe something to do with importing from a stored procedure? Since sumif works, is there a way to use sumif with two conditions in two different columns? Thank you for all your help. "Roger Govier" wrote: Hi Greg Try =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Normally, "," and "*" don't make a difference in checking multiple conditions. My suggestion was a result of desperation ......
<BG Epinn "Epinn" wrote in message ... I am very curious if changing the comma (,) between the "Jan" argument and "2006" argument to * will make a difference. I always use "*" to check for multiple conditions. =SUMPRODUCT(--($D$31:$D$10000&""="Jan")* --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) Epinn =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) "Greg Snidow" wrote in message ... Roger. When I tested the formula for only "Jan" it returned the correct value. When I tested it for only "2007" it returned the correct value after I removed the "" from 2007. But when I put them both together they do not work, so I am thinking I have a syntax error. When I did this test I made a new workbook and did not touch the format of anything. It seems all are general. This is very perplexing to me, so I thank all of you people very much who take the time to help foundlings like me. "Roger Govier" wrote: Hi Greg Although you are saying that the column D contains "Jan", is it text "Jan" or could it be 01/01/2006 formatted as mmm? What are the lengths returned by the cells in column D, are they all 3 ? =LEN(D2) Try breaking the formula down into pieces, and see if any parts return the correct values e.g. =SUMPRODUCT(--($D$31:$D$10000&""="Jan")) =SUMPRODUCT(--($E$31:$E$10000&""="2006")) Your comparative ranges are not the same as the data ranges to be summed. They are of equal dimension, so Sumproduct will not object, but is that what you intended? If you want to mail me a copy of the workbook, I will take a look. The previous problem was operator error. To mail direct, remove NOSPAM from my address. -- Regards Roger Govier "Greg Snidow" wrote in message ... Thanks Roger for also giving your input. I see from the other post that it can get heated. In any event I tried 1*myrange, and I still get a '0'. I have tried formating both the cell with the formula and the range to text and then general, but still '0'. I have tried deleting the contents then formating, then refreshing my data as EPINN suggested, bit still wont work. Is there maybe something to do with importing from a stored procedure? Since sumif works, is there a way to use sumif with two conditions in two different columns? Thank you for all your help. "Roger Govier" wrote: Hi Greg Try =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Thank you so much Roger. I never would have been able to discern such subtle
differences. I am just beginning to understand what is going on in the background, so I think it will be best if I just repost the old function along with the new. OLD(Does not work) =SUMPRODUCT(--($D$10:$D$1000 = "Jan"),($E$10:$E$1000 = "2007"),G10:G1000+J10:J1000+S12:S1002+Y12:Y100 2) NEW(Works,Thanks Roger) =SUMPRODUCT(--(D10:D1000="Jan"),--(E10:E1000=2007),G10:G1000+J10:J1000+S10:S1000+Y10 :Y1000) I can see the differences, my only question now is that if the format of both "Jan" and 2007 are general, how did you know they were being treated differently? "Roger Govier" wrote: Hi Greg Although you are saying that the column D contains "Jan", is it text "Jan" or could it be 01/01/2006 formatted as mmm? What are the lengths returned by the cells in column D, are they all 3 ? =LEN(D2) Try breaking the formula down into pieces, and see if any parts return the correct values e.g. =SUMPRODUCT(--($D$31:$D$10000&""="Jan")) =SUMPRODUCT(--($E$31:$E$10000&""="2006")) Your comparative ranges are not the same as the data ranges to be summed. They are of equal dimension, so Sumproduct will not object, but is that what you intended? If you want to mail me a copy of the workbook, I will take a look. The previous problem was operator error. To mail direct, remove NOSPAM from my address. -- Regards Roger Govier "Greg Snidow" wrote in message ... Thanks Roger for also giving your input. I see from the other post that it can get heated. In any event I tried 1*myrange, and I still get a '0'. I have tried formating both the cell with the formula and the range to text and then general, but still '0'. I have tried deleting the contents then formating, then refreshing my data as EPINN suggested, bit still wont work. Is there maybe something to do with importing from a stored procedure? Since sumif works, is there a way to use sumif with two conditions in two different columns? Thank you for all your help. "Roger Govier" wrote: Hi Greg Try =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
SUMPRODUCT trouble
Hi Greg
Well I think "Jan" was fairly obvious. Highlighting the cell with Jan in it, showed Jan, whereas if it had been a date (Numeric) formatted as mmm, in the formula bar I would have seen 01/01/06 or something similar. 2006 and 2007 looked like numeric (they are right justified, not that that can be taken as definitive - but its a first indication) Hovering over 2 cells (E10:E11) in the column, the function towards bottom right of the screen was displaying Sum = 4012 so it was pretty obviously numeric format. I could have used a blank cell somewhere on the sheet and typed =E10+1 and had it been text I would have had a #VALUE result, as it is numeric then I got 2007. As I said in my post directly back to you, your formula failed on 2 counts 1. Using "2007" as the comparator for a range of data containing numeric values 2. Even with the correct comparator of 2007, the True values were not being coerced to 1's as you were not preceding the test with the double unary minus, nor were you multiplying the tests together with "*" as the operator rather than "," As far as differences then occurring with the working formula's result, and that gained by doing a filter on the respective columns and adding the respective Subtotal(9,range) results, that was due to the last terms in the formula being ranges from 12 to 1002 as opposed to correctly being 10 to 1000. The underlying format of the cell is not necessarily an indicator of the cell contents. With a cell formatted General, entering the word Test or 1234 does not change its format from the default of General. However, entering something like 20/11/2006 does change the format to Date, or entering 17:30 does change the format to Custom hh:mm. If you then enter 1234 into the cell which has picked up the Date format, it will return 18/05/1903 or 1234 days after 31/12/1899 (and its format remains as Date), because it assumes you are entering a serial number which formatting is supposed to turn into a Date appearance. Entering the word Test, shows as Test but the format still shows as Date. In the cell that has picked up the Date format, entering Test shows Test and format remains as Custom hh:mm, entering 1234 shows 00:00. Frustrating isn't it? So don't look at the cell format to necessarily determine the type of the content, use a simple test. If you don't know whether the values are going to be Text or numeric, then adding the null string "" to each side of the test will cause it to work always, as you are forcing like for like comparisons of text. =SUMPRODUCT(--(D10:D1000&""="Jan"),--(E10:E1000&""=2007&""), G10:G1000+J10:J1000+S10:S1000+Y10:Y1000) or =SUMPRODUCT(--(D10:D1000&""="Jan"),--(E10:E1000&""="2007"), G10:G1000+J10:J1000+S10:S1000+Y10:Y1000) will work regardless. -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much Roger. I never would have been able to discern such subtle differences. I am just beginning to understand what is going on in the background, so I think it will be best if I just repost the old function along with the new. OLD(Does not work) =SUMPRODUCT(--($D$10:$D$1000 = "Jan"),($E$10:$E$1000 = "2007"),G10:G1000+J10:J1000+S12:S1002+Y12:Y100 2) NEW(Works,Thanks Roger) =SUMPRODUCT(--(D10:D1000="Jan"),--(E10:E1000=2007),G10:G1000+J10:J1000+S10:S1000+Y10 :Y1000) I can see the differences, my only question now is that if the format of both "Jan" and 2007 are general, how did you know they were being treated differently? "Roger Govier" wrote: Hi Greg Although you are saying that the column D contains "Jan", is it text "Jan" or could it be 01/01/2006 formatted as mmm? What are the lengths returned by the cells in column D, are they all 3 ? =LEN(D2) Try breaking the formula down into pieces, and see if any parts return the correct values e.g. =SUMPRODUCT(--($D$31:$D$10000&""="Jan")) =SUMPRODUCT(--($E$31:$E$10000&""="2006")) Your comparative ranges are not the same as the data ranges to be summed. They are of equal dimension, so Sumproduct will not object, but is that what you intended? If you want to mail me a copy of the workbook, I will take a look. The previous problem was operator error. To mail direct, remove NOSPAM from my address. -- Regards Roger Govier "Greg Snidow" wrote in message ... Thanks Roger for also giving your input. I see from the other post that it can get heated. In any event I tried 1*myrange, and I still get a '0'. I have tried formating both the cell with the formula and the range to text and then general, but still '0'. I have tried deleting the contents then formating, then refreshing my data as EPINN suggested, bit still wont work. Is there maybe something to do with importing from a stored procedure? Since sumif works, is there a way to use sumif with two conditions in two different columns? Thank you for all your help. "Roger Govier" wrote: Hi Greg Try =SUMPRODUCT(--($D$31:$D$10000&""="Jan"), --($E$31:$E$10000&""="2006"), H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002) -- Regards Roger Govier "Greg Snidow" wrote in message ... Thank you so much for your time and input. I maybe should have included in my first post that I used import data to import the data from a stored procedure on the back end of an .ADP database with SQL2k. The "Jan" is actually SELECT... CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is from a smalldatetime format on the backend. Would this make a difference? I tested out sumif on the same data and it worked. I am concerned about having to format the cells more than once, because I need to set the data range properties to refresh data on file open. I thank you so much for your help. Oh, also I did try to make it work by restricting the range to only 200 rows, but it still did not work. "Epinn" wrote: Wonder why. << I did more testing and I think I am on to something. If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem. But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text. If you key in "apple" instead of a number, it will be treated as TEXT regardless. I am going to start my own thread and hopefully find out why this is happening. Interesting. Epinn "Epinn" wrote in message ... I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006". Wonder why. You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click toolsformula auditingevaluate formula to see the steps unfold. Epinn "Greg Snidow" wrote in message ... Greetings all. I am very new to worksheet formulas and I am having trouble getting sumproduct to work. here is my formula, as I found it in many places he =SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33 :AF10002,AR33:AR10002) basically I have a month and year column, both formatted as text, then several other columns formatted as currency, and some as number. All I get are zeros. Thank you in advance, I am flumoxed. Also what does the $ do. |
| All times are GMT +1. The time now is 07:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com