Find and Calculate Function?

[Guest]

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 today’s 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 can’t figure out how to add the minutes
that are below each instance in the same function. Can this be done?

 

[Guest]

Is the data organised thus (down a column)?

Date
Time (Minutes)
Date
Time (Minutes)

.....

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Seems to be some confusion (at least with me!) over rows vs columns!

Date in row 2, times in row 3:

=SUMPRODUCT(--(B22=X2),B33)

HTH

 

[Guest]

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?

 

[Guest]

=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!)

 

[Guest]

I had to tweak it just a bit but I think it's working. I really appreciate
your help..