selecting days of week using a formula in spreadsheets

T

Terry

hi trying to select days of week in spreadsheet amount of each day varies
each week and would like to use formula instead of manually etering cell
references each day.
 
B

Bob Phillips

You need to clarify what you mean by select. A formula cannot be used to
work out all instances of a day and then select them in the classic sense,
but it can say add all items for a particular day.

More detail, and example data.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
T

Terry

I have set up a weekly workbook which records mileage, amount of fuel, hours
worked to name a few. What I'm trying to achieve is I send drivers out each
day to different location. I have a sheet for each location, but collections
very at each location each day so Sunday may be A17:A21 Monday A22:A30 which
is fine to enter for that week but the following week Sunday may be A17 and
Monday A18:A21. I need to obtain total Mileage, toatal fuel used and total
hours for each day.
 
B

Bob Phillips

If you enter the date in a column as well it would be an easy summation
formula. Otherwise what rule determines that Sunday is A17 or A17:A21?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
T

Terry

I enter days off week in Column A. mileage in column U Fuel in column T and
time in column N
Currently I use a space at bottom of work sheet to calculate sunday monday

So would put undar sunday
=sum(U17:U21) which may give result 1724
=sum(T17:T21) which may give result 675.95
=A50/A51 which gives 2.54 kms per Litre
=sum(N17:N21) giving hours worked

under monday
=sum(U22:U25)
=sum(T22:T25)
=B50/B51
=sum(N22:N25)

what I want to do is for execel to decide what day it is and put it in right
column so I do not need to change row numbers each week.
 
B

Bob Phillips

=SUMIF(A2:A100,"Sunday",U2:U100) gives the time total for Sunday, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
T

Terry

Excellent just tried it. works brilliantly, does what I want it to do.
many thanks Bob

Terry
 
Top