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?

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?

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?

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?

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?

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?

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?

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?