Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find dates to calculate amount of work per day? Possible?
I've been reading the discussions trying to find an instance where someone
has asked about this before and I can't seem to find anything. The sheet that I have has the date that the work was done and how many units were completed per map section. So there could be multiple rows with that date on it. What I'm trying to figure out is if there is a way to have Excel search for a date and then get all of the units done that day, even if they are on multiple rows. Then I can calculate how many units per day are being done. I don't know how to do VBA, so everything I've been doing is using cell formulas. Does anyone have any suggestions? Thank you in advance for your help! |
#2
|
|||
|
|||
Find dates to calculate amount of work per day? Possible?
Perhaps the SUMIF function will work for you? If not, certainly a SUMPRODUCT
formula could be devised. Post back if sumif doesn't do it. -- Don Guillett SalesAid Software "TKGerdie" wrote in message ... I've been reading the discussions trying to find an instance where someone has asked about this before and I can't seem to find anything. The sheet that I have has the date that the work was done and how many units were completed per map section. So there could be multiple rows with that date on it. What I'm trying to figure out is if there is a way to have Excel search for a date and then get all of the units done that day, even if they are on multiple rows. Then I can calculate how many units per day are being done. I don't know how to do VBA, so everything I've been doing is using cell formulas. Does anyone have any suggestions? Thank you in advance for your help! |
#4
|
|||
|
|||
Find dates to calculate amount of work per day? Possible?
You can use SUMIF to get a total of the units on a date
=SUMIF(A:A,--"2005-11-01",B:B) You can get a count with =COUNTIF(A:A,--"2005-11-01") and thus get the average with =SUMIF(A:A,--"2005-11-01",B:B)/COUNTIF(A:A,--"2005-11-01") or you can do it directly with an array formula =AVERAGE(IF(A1:A1000=--"2005-11-01",B1:B1000)) which is committed with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "TKGerdie" wrote in message ... I've been reading the discussions trying to find an instance where someone has asked about this before and I can't seem to find anything. The sheet that I have has the date that the work was done and how many units were completed per map section. So there could be multiple rows with that date on it. What I'm trying to figure out is if there is a way to have Excel search for a date and then get all of the units done that day, even if they are on multiple rows. Then I can calculate how many units per day are being done. I don't know how to do VBA, so everything I've been doing is using cell formulas. Does anyone have any suggestions? Thank you in advance for your help! |
#5
|
|||
|
|||
Find dates to calculate amount of work per day? Possible?
See Bob's post and use a cell reference instead of typing in the date.
=sumif(range,a1,range) -- Don Guillett SalesAid Software "TKGerdie" wrote in message ... The SUMIF works. However, my criteria is =10/24/05 so that I can get the sum of the work done on that day. I want to use this same formula for every work day through the end of the year. Is there an easier way to do this than typing the date into the formula every time I carry it into a new cell? "Don Guillett" wrote: Perhaps the SUMIF function will work for you? If not, certainly a SUMPRODUCT formula could be devised. Post back if sumif doesn't do it. -- Don Guillett SalesAid Software "TKGerdie" wrote in message ... I've been reading the discussions trying to find an instance where someone has asked about this before and I can't seem to find anything. The sheet that I have has the date that the work was done and how many units were completed per map section. So there could be multiple rows with that date on it. What I'm trying to figure out is if there is a way to have Excel search for a date and then get all of the units done that day, even if they are on multiple rows. Then I can calculate how many units per day are being done. I don't know how to do VBA, so everything I've been doing is using cell formulas. Does anyone have any suggestions? Thank you in advance for your help! |
#6
|
|||
|
|||
Find dates to calculate amount of work per day? Possible?
Thank you very much for the help from both of you. It is much appreciated.
And much easier on the head instead of banging it on the desk. Have a great day. "Don Guillett" wrote: See Bob's post and use a cell reference instead of typing in the date. =sumif(range,a1,range) -- Don Guillett SalesAid Software "TKGerdie" wrote in message ... The SUMIF works. However, my criteria is =10/24/05 so that I can get the sum of the work done on that day. I want to use this same formula for every work day through the end of the year. Is there an easier way to do this than typing the date into the formula every time I carry it into a new cell? "Don Guillett" wrote: Perhaps the SUMIF function will work for you? If not, certainly a SUMPRODUCT formula could be devised. Post back if sumif doesn't do it. -- Don Guillett SalesAid Software "TKGerdie" wrote in message ... I've been reading the discussions trying to find an instance where someone has asked about this before and I can't seem to find anything. The sheet that I have has the date that the work was done and how many units were completed per map section. So there could be multiple rows with that date on it. What I'm trying to figure out is if there is a way to have Excel search for a date and then get all of the units done that day, even if they are on multiple rows. Then I can calculate how many units per day are being done. I don't know how to do VBA, so everything I've been doing is using cell formulas. Does anyone have any suggestions? Thank you in advance for your help! |
#7
|
|||
|
|||
Find dates to calculate amount of work per day? Possible?
TKGerdie Wrote: I've been reading the discussions trying to find an instance where someone has asked about this before and I can't seem to find anything. The sheet that I have has the date that the work was done and how many units were completed per map section. So there could be multiple rows with that date on it. What I'm trying to figure out is if there is a way to have Excel search for a date and then get all of the units done that day, even if they are on multiple rows. Then I can calculate how many units per day are being done. I don't know how to do VBA, so everything I've been doing is using cell formulas. Does anyone have any suggestions? Thank you in advance for your help! Hi TKGerdie Really need a bit more info about your data to give a truly informed response, but you could look at using a Pivot Table or the SUMIF function -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=480846 |
#8
|
|||
|
|||
Find dates to calculate amount of work per day? Possible?
Glad to hear you won't have a headache
-- Don Guillett SalesAid Software "TKGerdie" wrote in message ... Thank you very much for the help from both of you. It is much appreciated. And much easier on the head instead of banging it on the desk. Have a great day. "Don Guillett" wrote: See Bob's post and use a cell reference instead of typing in the date. =sumif(range,a1,range) -- Don Guillett SalesAid Software "TKGerdie" wrote in message ... The SUMIF works. However, my criteria is =10/24/05 so that I can get the sum of the work done on that day. I want to use this same formula for every work day through the end of the year. Is there an easier way to do this than typing the date into the formula every time I carry it into a new cell? "Don Guillett" wrote: Perhaps the SUMIF function will work for you? If not, certainly a SUMPRODUCT formula could be devised. Post back if sumif doesn't do it. -- Don Guillett SalesAid Software "TKGerdie" wrote in message ... I've been reading the discussions trying to find an instance where someone has asked about this before and I can't seem to find anything. The sheet that I have has the date that the work was done and how many units were completed per map section. So there could be multiple rows with that date on it. What I'm trying to figure out is if there is a way to have Excel search for a date and then get all of the units done that day, even if they are on multiple rows. Then I can calculate how many units per day are being done. I don't know how to do VBA, so everything I've been doing is using cell formulas. Does anyone have any suggestions? Thank you in advance for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
calculate interest between two dates | Excel Worksheet Functions | |||
To find a combination of numbers that equal a set amount? | Excel Discussion (Misc queries) | |||
calculate with dates before 1-1-1900 | Excel Worksheet Functions | |||
How do I ensure dates inputted are during the work week? | Excel Worksheet Functions |