Find dates to calculate amount of work per day? Possible?

T

TKGerdie

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!
 
D

Don Guillett

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.
 
T

TKGerdie

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?
 
B

Bob Phillips

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)
 
D

Don Guillett

See Bob's post and use a cell reference instead of typing in the date.
=sumif(range,a1,range)
 
T

TKGerdie

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.
 
P

Paul Sheppard

TKGerdie said:
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!
Click to expand...

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
 
Top