Find and Calculate Function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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
 
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
 
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?
 
=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!)
 
Back
Top