Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Calculate Function?
I have a spreadsheet I have built to keep track of jobs and the amount of
time worked on each. I have about 5 rows of data showing dates and time in minutes worked on for each job number. My question is I am trying to create a formula that will check a range of data for a date and then add up the amount of minutes in the cell below it. For example, I would have todays date 7/23/07 then in the cell directly below it I will have the value 28 for 28 minutes. I did a =countif( ) function to find how many instances of the date were shown in the range however I cant figure out how to add the minutes that are below each instance in the same function. Can this be done? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Calculate Function?
Is the data organised thus (down a column)?
Date Time (Minutes) Date Time (Minutes) ..... "tpayne" wrote: I have a spreadsheet I have built to keep track of jobs and the amount of time worked on each. I have about 5 rows of data showing dates and time in minutes worked on for each job number. My question is I am trying to create a formula that will check a range of data for a date and then add up the amount of minutes in the cell below it. For example, I would have todays date 7/23/07 then in the cell directly below it I will have the value 28 for 28 minutes. I did a =countif( ) function to find how many instances of the date were shown in the range however I cant figure out how to add the minutes that are below each instance in the same function. Can this be done? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Calculate Function?
yes,
I have 5 columns: then 2 rows per job: 7/23/07 | 7/23/07 | 7/24/07 27 | 15 | 13 shows as minutes can be added to a job multiple times a day if the project is worked on. I would like a function that can find in a large range those 2 dates and add up the amounts below them to show 42 minutes "Toppers" wrote: Is the data organised thus (down a column)? Date Time (Minutes) Date Time (Minutes) .... "tpayne" wrote: I have a spreadsheet I have built to keep track of jobs and the amount of time worked on each. I have about 5 rows of data showing dates and time in minutes worked on for each job number. My question is I am trying to create a formula that will check a range of data for a date and then add up the amount of minutes in the cell below it. For example, I would have todays date 7/23/07 then in the cell directly below it I will have the value 28 for 28 minutes. I did a =countif( ) function to find how many instances of the date were shown in the range however I cant figure out how to add the minutes that are below each instance in the same function. Can this be done? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Calculate Function?
try (assuming data is as per my previous posting):
=SUMPRODUCT(--($A$2:$A$14=X2),$A$3:$A$15) X2 contains your date A2 is first cell with date, A14 last date "Toppers" wrote: Is the data organised thus (down a column)? Date Time (Minutes) Date Time (Minutes) .... "tpayne" wrote: I have a spreadsheet I have built to keep track of jobs and the amount of time worked on each. I have about 5 rows of data showing dates and time in minutes worked on for each job number. My question is I am trying to create a formula that will check a range of data for a date and then add up the amount of minutes in the cell below it. For example, I would have todays date 7/23/07 then in the cell directly below it I will have the value 28 for 28 minutes. I did a =countif( ) function to find how many instances of the date were shown in the range however I cant figure out how to add the minutes that are below each instance in the same function. Can this be done? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Calculate Function?
Seems to be some confusion (at least with me!) over rows vs columns!
Date in row 2, times in row 3: =SUMPRODUCT(--(B2:D2=X2),B3:D3) HTH "tpayne" wrote: yes, I have 5 columns: then 2 rows per job: 7/23/07 | 7/23/07 | 7/24/07 27 | 15 | 13 shows as minutes can be added to a job multiple times a day if the project is worked on. I would like a function that can find in a large range those 2 dates and add up the amounts below them to show 42 minutes "Toppers" wrote: Is the data organised thus (down a column)? Date Time (Minutes) Date Time (Minutes) .... "tpayne" wrote: I have a spreadsheet I have built to keep track of jobs and the amount of time worked on each. I have about 5 rows of data showing dates and time in minutes worked on for each job number. My question is I am trying to create a formula that will check a range of data for a date and then add up the amount of minutes in the cell below it. For example, I would have todays date 7/23/07 then in the cell directly below it I will have the value 28 for 28 minutes. I did a =countif( ) function to find how many instances of the date were shown in the range however I cant figure out how to add the minutes that are below each instance in the same function. Can this be done? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Calculate Function?
That formula doesn't seem to have a find calc in it. My Range of data is from
say cells v3:v711 and across to z3:z711 in that range. So there are 5 columns, and many rows. The data is all arranged date over time across the row. Multiple projects could be worked on a day so there would be multiple rows having the same date and an associated time below. Does this make any better sense? "Toppers" wrote: try (assuming data is as per my previous posting): =SUMPRODUCT(--($A$2:$A$14=X2),$A$3:$A$15) X2 contains your date A2 is first cell with date, A14 last date "Toppers" wrote: Is the data organised thus (down a column)? Date Time (Minutes) Date Time (Minutes) .... "tpayne" wrote: I have a spreadsheet I have built to keep track of jobs and the amount of time worked on each. I have about 5 rows of data showing dates and time in minutes worked on for each job number. My question is I am trying to create a formula that will check a range of data for a date and then add up the amount of minutes in the cell below it. For example, I would have todays date 7/23/07 then in the cell directly below it I will have the value 28 for 28 minutes. I did a =countif( ) function to find how many instances of the date were shown in the range however I cant figure out how to add the minutes that are below each instance in the same function. Can this be done? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Calculate Function?
=SUMPRODUCT(--(V3:Z710=DATE(2007,7,23)),(V4:Z711))
Assuming first date is in V3 (i.e row 3) and data in row 4 This will calculate for all dates of 23/07/2007 (UK format!) "tpayne" wrote: That formula doesn't seem to have a find calc in it. My Range of data is from say cells v3:v711 and across to z3:z711 in that range. So there are 5 columns, and many rows. The data is all arranged date over time across the row. Multiple projects could be worked on a day so there would be multiple rows having the same date and an associated time below. Does this make any better sense? "Toppers" wrote: try (assuming data is as per my previous posting): =SUMPRODUCT(--($A$2:$A$14=X2),$A$3:$A$15) X2 contains your date A2 is first cell with date, A14 last date "Toppers" wrote: Is the data organised thus (down a column)? Date Time (Minutes) Date Time (Minutes) .... "tpayne" wrote: I have a spreadsheet I have built to keep track of jobs and the amount of time worked on each. I have about 5 rows of data showing dates and time in minutes worked on for each job number. My question is I am trying to create a formula that will check a range of data for a date and then add up the amount of minutes in the cell below it. For example, I would have todays date 7/23/07 then in the cell directly below it I will have the value 28 for 28 minutes. I did a =countif( ) function to find how many instances of the date were shown in the range however I cant figure out how to add the minutes that are below each instance in the same function. Can this be done? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Calculate Function?
I had to tweak it just a bit but I think it's working. I really appreciate
your help.. "Toppers" wrote: =SUMPRODUCT(--(V3:Z710=DATE(2007,7,23)),(V4:Z711)) Assuming first date is in V3 (i.e row 3) and data in row 4 This will calculate for all dates of 23/07/2007 (UK format!) "tpayne" wrote: That formula doesn't seem to have a find calc in it. My Range of data is from say cells v3:v711 and across to z3:z711 in that range. So there are 5 columns, and many rows. The data is all arranged date over time across the row. Multiple projects could be worked on a day so there would be multiple rows having the same date and an associated time below. Does this make any better sense? "Toppers" wrote: try (assuming data is as per my previous posting): =SUMPRODUCT(--($A$2:$A$14=X2),$A$3:$A$15) X2 contains your date A2 is first cell with date, A14 last date "Toppers" wrote: Is the data organised thus (down a column)? Date Time (Minutes) Date Time (Minutes) .... "tpayne" wrote: I have a spreadsheet I have built to keep track of jobs and the amount of time worked on each. I have about 5 rows of data showing dates and time in minutes worked on for each job number. My question is I am trying to create a formula that will check a range of data for a date and then add up the amount of minutes in the cell below it. For example, I would have todays date 7/23/07 then in the cell directly below it I will have the value 28 for 28 minutes. I did a =countif( ) function to find how many instances of the date were shown in the range however I cant figure out how to add the minutes that are below each instance in the same function. Can this be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate function Xx+1=rX(1-X). How? | Excel Discussion (Misc queries) | |||
find the same entries and calculate sum | Excel Worksheet Functions | |||
Find dates to calculate amount of work per day? Possible? | Excel Discussion (Misc queries) | |||
Find duplicates then calculate..??? | Excel Worksheet Functions | |||
Function Won't Calculate -- Sometimes | Excel Discussion (Misc queries) |