Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
I have a spread sheet with for simplicity 4 columns
Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company? An example or link to an example would be appreciated. Regards Dermot |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
=SUMPRODUCT(--(A2:A200=--"2005-01-01"),--(A2:A200<=--"2005-03-31"),--(B2:b2
00="company_name"),(C2:C200+D2:D200)) -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... I have a spread sheet with for simplicity 4 columns Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company? An example or link to an example would be appreciated. Regards Dermot |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Thanks for the reply Bob,
I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. Please advise further Regards Dermot "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200=--"2005-01-01"),--(A2:A200<=--"2005-03-31"),--(B2:b2 00="company_name"),(C2:C200+D2:D200)) -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... I have a spread sheet with for simplicity 4 columns Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company? An example or link to an example would be appreciated. Regards Dermot |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Hi Bob,
I tried entering the example formula but I get an error message. I was prompted to use the formula construction below SUMPRODUCT(Array 1,[Array2],[Array3],...) so I entered =SUMPRODUCT(A2:A200= --"2005-01-01",[A2:A200,<= "2005-03-31"],[B2:B200="company_name"],[C2:C200+D2:D200]) I wasn't too sure about the dashes you have included --"2005-01-01" Can you tell me what they do? Thought I would post this to let you know where I am at with my problem. Regards Dermot Any other advise regarding my previous posting will be much appreciated. Thanks Regards Dermot "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200=--"2005-01-01"),--(A2:A200<=--"2005-03-31"),--(B2:b2 00="company_name"),(C2:C200+D2:D200)) -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... I have a spread sheet with for simplicity 4 columns Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company? An example or link to an example would be appreciated. Regards Dermot |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
"Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
"Dermot" wrote in message ... Hi Bob, I tried entering the example formula but I get an error message. I was prompted to use the formula construction below SUMPRODUCT(Array 1,[Array2],[Array3],...) so I entered =SUMPRODUCT(A2:A200= --"2005-01-01",[A2:A200,<= "2005-03-31"],[B2:B200="company_name"],[C2:C200+D2:D200]) The format you are seeing is the standard SUMPRODUCT formula, what I gave you is a variation you will not see documented in the help files. The page I gave in the last post gives a good explanation. It is important that you neter the formula as supplied, if you muck about with it, don't be surprised if it doesn't work. The only thing you should change are the real cell ranges, and the test values, company name & dates. The formula should be =SUMPRODUCT(A2:A200= --"2005-01-01",(A2:A200,<=--"2005-03-31"),(B2:B200="co mpany_name"),(C2:C200+D2:D200)) When you see [...] in help, it doesn't mean that you use the square brackets, it means that those parameters are optional. I wasn't too sure about the dashes you have included --"2005-01-01" Can you tell me what they do? I use the -- to coerce the text date string into an Excel date that the formula can work with. Some people use DATE(2005,01,01), that is just my preference, as it is slicker and uses an ISO standard date format, as I am UK based we suffer with Excel dates, so an ISO standard helps to make our life easier. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Thanks for the link Bob ( Lots to get through here) and explanation.
I have tried to find the posting dated the 8-nov but I always find it difficult searching for previous postings if I have overlooked keeping my notification email. Can you send me a link to it? Thanks Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Thanks again Bob,
Quote It is important that you neter the formula as supplied, if you muck about with it, don't be surprised if it doesn't work. I did initially enter the formula "Exactly" as you supplied and it didn't work for me, but I think this may be do do with the cell references. I didn't really understand what I was doing. You have given me a lot of additional information to aid my better understanding so I will read spend some time reviewing it all. Thanks for the comments about the UK dates. I am in the UK too and briefly wondered about this...thanks. Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Hi Bob, I tried entering the example formula but I get an error message. I was prompted to use the formula construction below SUMPRODUCT(Array 1,[Array2],[Array3],...) so I entered =SUMPRODUCT(A2:A200= --"2005-01-01",[A2:A200,<= "2005-03-31"],[B2:B200="company_name"],[C2:C200+D2:D200]) The format you are seeing is the standard SUMPRODUCT formula, what I gave you is a variation you will not see documented in the help files. The page I gave in the last post gives a good explanation. It is important that you neter the formula as supplied, if you muck about with it, don't be surprised if it doesn't work. The only thing you should change are the real cell ranges, and the test values, company name & dates. The formula should be =SUMPRODUCT(A2:A200= --"2005-01-01",(A2:A200,<=--"2005-03-31"),(B2:B200="co mpany_name"),(C2:C200+D2:D200)) When you see [...] in help, it doesn't mean that you use the square brackets, it means that those parameters are optional. I wasn't too sure about the dashes you have included --"2005-01-01" Can you tell me what they do? I use the -- to coerce the text date string into an Excel date that the formula can work with. Some people use DATE(2005,01,01), that is just my preference, as it is slicker and uses an ISO standard date format, as I am UK based we suffer with Excel dates, so an ISO standard helps to make our life easier. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Hi Bob.
A few more questions now that I have had a proper look at the information. I have entered the following formula into and appropriate cell on a small test sheet I made upexacly as specified. Then I tried it modified ,to refer to cells which contain the dates as below, but no joy...get an error message. =SUMPRODUCT(--(A2:A6= --"=G3",(A2:A6,F3< = -- "=H3"),--(B2:B6="=F3"),(C2:C6+D2:D6)) G3 is the start date cell, H3 is the End Date Cell, F3 is the company cell. I formatted A2 to A6 to date format 3.2.2005 am not sure if this is correct as when the error is generated a blue rectangle appears arround the date column. Quote If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. How whould I do incorporate all start, end dates for all quarters. Maybbe a bit ahead of myself here when I haven't got the supplied equation to work yet!! Regards Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Hi Bob
I meant to post this basic shhet to dmonstrate what I am getting. Date Company Name Amount X Amount Y Quarterly Totals Due 3.1.05 co 1 10 10 Company Start Date End Date Amount Due 5.1.05 co2 10 10 2.2.05 c01 10 10 5.3.05 c03 10 10 23.3.05 c02 10 10 "Bob Phillips" wrote: "Dermot" wrote in message ... Hi Bob, I tried entering the example formula but I get an error message. I was prompted to use the formula construction below SUMPRODUCT(Array 1,[Array2],[Array3],...) so I entered =SUMPRODUCT(A2:A200= --"2005-01-01",[A2:A200,<= "2005-03-31"],[B2:B200="company_name"],[C2:C200+D2:D200]) The format you are seeing is the standard SUMPRODUCT formula, what I gave you is a variation you will not see documented in the help files. The page I gave in the last post gives a good explanation. It is important that you neter the formula as supplied, if you muck about with it, don't be surprised if it doesn't work. The only thing you should change are the real cell ranges, and the test values, company name & dates. The formula should be =SUMPRODUCT(A2:A200= --"2005-01-01",(A2:A200,<=--"2005-03-31"),(B2:B200="co mpany_name"),(C2:C200+D2:D200)) When you see [...] in help, it doesn't mean that you use the square brackets, it means that those parameters are optional. I wasn't too sure about the dashes you have included --"2005-01-01" Can you tell me what they do? I use the -- to coerce the text date string into an Excel date that the formula can work with. Some people use DATE(2005,01,01), that is just my preference, as it is slicker and uses an ISO standard date format, as I am UK based we suffer with Excel dates, so an ISO standard helps to make our life easier. |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Yeah, let's concentrate on getting something working Dermot.
First, enter the dates in A2:A6 in the format 03/02/2005. Dots will only work if you have some special setting. Secondly, in the formula, when referring to a date cell you don't need to coerce nit to a date (It is already done)< and you don't need quotes, and similarly for testing the company name test as a cell, so it is just =SUMPRODUCT(--(A2:A6=G3,(A2:A6,F3<= H3),--(B2:B6=F3),(C2:C6+D2:D6)) -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... Hi Bob. A few more questions now that I have had a proper look at the information. I have entered the following formula into and appropriate cell on a small test sheet I made upexacly as specified. Then I tried it modified ,to refer to cells which contain the dates as below, but no joy...get an error message. =SUMPRODUCT(--(A2:A6= --"=G3",(A2:A6,F3< = -- "=H3"),--(B2:B6="=F3"),(C2:C6+D2:D6)) G3 is the start date cell, H3 is the End Date Cell, F3 is the company cell. I formatted A2 to A6 to date format 3.2.2005 am not sure if this is correct as when the error is generated a blue rectangle appears arround the date column. Quote If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. How whould I do incorporate all start, end dates for all quarters. Maybbe a bit ahead of myself here when I haven't got the supplied equation to work yet!! Regards Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
It is http://tinyurl.com/da27l
-- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... Thanks for the link Bob ( Lots to get through here) and explanation. I have tried to find the posting dated the 8-nov but I always find it difficult searching for previous postings if I have overlooked keeping my notification email. Can you send me a link to it? Thanks Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Hi Bob,
I missed this posting and posted a lot of rubbish in the two postings at the end. Please ignore them. I will review all the information you have given me todate. Is it okay with you, for me to post back here, if I have any further questions relating to this problem? Regards Dermot "Bob Phillips" wrote: Yeah, let's concentrate on getting something working Dermot. First, enter the dates in A2:A6 in the format 03/02/2005. Dots will only work if you have some special setting. Secondly, in the formula, when referring to a date cell you don't need to coerce nit to a date (It is already done)< and you don't need quotes, and similarly for testing the company name test as a cell, so it is just =SUMPRODUCT(--(A2:A6=G3,(A2:A6,F3<= H3),--(B2:B6=F3),(C2:C6+D2:D6)) -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... Hi Bob. A few more questions now that I have had a proper look at the information. I have entered the following formula into and appropriate cell on a small test sheet I made upexacly as specified. Then I tried it modified ,to refer to cells which contain the dates as below, but no joy...get an error message. =SUMPRODUCT(--(A2:A6= --"=G3",(A2:A6,F3< = -- "=H3"),--(B2:B6="=F3"),(C2:C6+D2:D6)) G3 is the start date cell, H3 is the End Date Cell, F3 is the company cell. I formatted A2 to A6 to date format 3.2.2005 am not sure if this is correct as when the error is generated a blue rectangle appears arround the date column. Quote If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. How whould I do incorporate all start, end dates for all quarters. Maybbe a bit ahead of myself here when I haven't got the supplied equation to work yet!! Regards Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Of course, I will keep watching it.
-- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... Hi Bob, I missed this posting and posted a lot of rubbish in the two postings at the end. Please ignore them. I will review all the information you have given me todate. Is it okay with you, for me to post back here, if I have any further questions relating to this problem? Regards Dermot "Bob Phillips" wrote: Yeah, let's concentrate on getting something working Dermot. First, enter the dates in A2:A6 in the format 03/02/2005. Dots will only work if you have some special setting. Secondly, in the formula, when referring to a date cell you don't need to coerce nit to a date (It is already done)< and you don't need quotes, and similarly for testing the company name test as a cell, so it is just =SUMPRODUCT(--(A2:A6=G3,(A2:A6,F3<= H3),--(B2:B6=F3),(C2:C6+D2:D6)) -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... Hi Bob. A few more questions now that I have had a proper look at the information. I have entered the following formula into and appropriate cell on a small test sheet I made upexacly as specified. Then I tried it modified ,to refer to cells which contain the dates as below, but no joy...get an error message. =SUMPRODUCT(--(A2:A6= --"=G3",(A2:A6,F3< = -- "=H3"),--(B2:B6="=F3"),(C2:C6+D2:D6)) G3 is the start date cell, H3 is the End Date Cell, F3 is the company cell. I formatted A2 to A6 to date format 3.2.2005 am not sure if this is correct as when the error is generated a blue rectangle appears arround the date column. Quote If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. How whould I do incorporate all start, end dates for all quarters. Maybbe a bit ahead of myself here when I haven't got the supplied equation to work yet!! Regards Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Hi Bob,
I have read through and have obtained a great understanding of the versatility of "SUMPRODUCT" Thanks again. The link explains all very clearly. One further question regarding the information on the linked page.....although it is not relevent to the initial question in this topic....I do not know any other way to explain the question...please advise..... Question http://www.xldynamic.com/source/xld.SUMPRODUCT.html I noticed that the tables i.e. Table 2 ......use a double border effect on the cells, I wondered if this had been applied from within excel....I can't find a method to do it in excel....or were the examples in this tutorial produced in another application? Thanks Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#16
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Hi Dermot,
No, that is jsut an HTML table border. All hand-crafted with TextPad. -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... Hi Bob, I have read through and have obtained a great understanding of the versatility of "SUMPRODUCT" Thanks again. The link explains all very clearly. One further question regarding the information on the linked page.....although it is not relevent to the initial question in this topic....I do not know any other way to explain the question...please advise..... Question http://www.xldynamic.com/source/xld.SUMPRODUCT.html I noticed that the tables i.e. Table 2 ......use a double border effect on the cells, I wondered if this had been applied from within excel....I can't find a method to do it in excel....or were the examples in this tutorial produced in another application? Thanks Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Thanks Bob
Best wishes Dermot "Bob Phillips" wrote: Hi Dermot, No, that is jsut an HTML table border. All hand-crafted with TextPad. -- HTH RP (remove nothere from the email address if mailing direct) "Dermot" wrote in message ... Hi Bob, I have read through and have obtained a great understanding of the versatility of "SUMPRODUCT" Thanks again. The link explains all very clearly. One further question regarding the information on the linked page.....although it is not relevent to the initial question in this topic....I do not know any other way to explain the question...please advise..... Question http://www.xldynamic.com/source/xld.SUMPRODUCT.html I noticed that the tables i.e. Table 2 ......use a double border effect on the cells, I wondered if this had been applied from within excel....I can't find a method to do it in excel....or were the examples in this tutorial produced in another application? Thanks Dermot "Bob Phillips" wrote: "Dermot" wrote in message ... Thanks for the reply Bob, I have looked up the SUMPRODUCT Function in my excel inside out book to better. I have yet to try the formula but wonder if you could clarify a few questions for me? 1. Can you write a verbal interpretation of the formula you have supplied me so I can better understand the reasoning behind it? See http://www.xldynamic.com/source/xld.SUMPRODUCT.html 2. Using your supplied formula. Can it be place in any cell or does it have to be a specific cell to ensure the correct cell references? No, as long as your refer back to the correct cells, it can be placed anywhere. 3. The Dates you have entered. How are these entered? Can they be changed for different periods throughout the year? Does the individual using the spreadsheet need to modify the formula or just enter a start date and end date, and then the calculation would be automatic? The intended users are not too familiar with excel, and I am not sure what I can and cannot do with excel regarding this problem. What I gave you was two example dates to test between. It can be changed to any two dates that you want. I used ISO standard date formats, yyyy-mm-dd, either change the date such as --"2005-01-01" by something other date, or put the date in a cell and refer to that. If you know the periods, that is if they are fixed, you could code all of them directly into the spreadsheet, saving the users any need to change it. I do need to find a solution Bob and appreciate your contribution. You supplied me with a brief formla for another problem, which resolved my problem, but it took me a while before I undestood what I was doing. I have just Googled that. I replied to something on 8th Nov, and I see that you responded 10 days later. I didn't see that in the NGs. Do you still need help on that one? |
#18
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Hi Bob
I sent an email to your email address not sure if you accept emails from newsgroup posters? I attached my excel attempt to understand SUMPRODUCT....but I haven't managed to get it to work for me yet. Would you be able to send me a working example as discussed in this posting? Thanks Dermot "Dermot" wrote: I have a spread sheet with for simplicity 4 columns Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company? An example or link to an example would be appreciated. Regards Dermot |
#19
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Do you realize that you have just posted Bob's e-mail address on the web?
Why do you think that we disguise our addresses? So that we love to get TONS of spam? IMHO, you have done him a *GREAT* disservice ! ! ! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dermot" wrote in message ... Hi Bob I sent an email to your email address not sure if you accept emails from newsgroup posters? I attached my excel attempt to understand SUMPRODUCT....but I haven't managed to get it to work for me yet. Would you be able to send me a working example as discussed in this posting? Thanks Dermot "Dermot" wrote: I have a spread sheet with for simplicity 4 columns Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company? An example or link to an example would be appreciated. Regards Dermot |
#20
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Caculating Columns Between Certain Dates
Yes I noticed what I did after the event...a bit thoughtless
Sorry about this Bob Regards Dermot "Ragdyer" wrote: Do you realize that you have just posted Bob's e-mail address on the web? Why do you think that we disguise our addresses? So that we love to get TONS of spam? IMHO, you have done him a *GREAT* disservice ! ! ! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dermot" wrote in message ... Hi Bob I sent an email to your email address not sure if you accept emails from newsgroup posters? I attached my excel attempt to understand SUMPRODUCT....but I haven't managed to get it to work for me yet. Would you be able to send me a working example as discussed in this posting? Thanks Dermot "Dermot" wrote: I have a spread sheet with for simplicity 4 columns Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company? An example or link to an example would be appreciated. Regards Dermot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTables - Group Dates, excluding dates | Excel Discussion (Misc queries) | |||
Convert three separate columns of values to dates | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) |