How to count by monday, tuesday, ...in a year?

[happybird66]

I'm curently working on the spreadsheet to count how many reservations
made by Monday, Tuesday, Wednsday, etc. Yes, I work at hospitablity
business, we want to know the production(reservation) made by each day
so we can better arrange the staffs.

Since, we are looking at resevation date not the check in date, so
those reservations weren't necessarily made in the same month. some of
them can be back to 6 months ago. I don't know which function in excel
could generate data from the format of " Sunday, Febuary 21, 2006" and
sum the day by "Monday, tuesday, .." I hope my question is clear.
Thank you for your help.

 

[RichardSchollar]

You could use:

=SUMPRODUCT(--(TEXT(A1:A1000,"dddd")="Monday"))

to count how many Mondays there are in cells A1:A1000. The criteria
can be a cell reference (ie a cell containing Monday, or Tuesday,
etc...). Adjust your range to suit.

Hope this helps!

Richard

 

[Don Guillett]

where col A is properly formatted dates and e1 has the date desired. Weekday
2 is Monday
=SUMPRODUCT((A3:A33>E1)*(WEEKDAY(A3:A33)=2))

 

[happybird66]

Thank you very much Richard. Your method works great! Just wonder how
you did that, where I can find more information if I want to learn more
about things like this?

Thanks again.

Julia

 

[happybird66]

Don,

I'm not quite sure what e1 means, but thank you anyway for looking at
my question. My problem is solved by using Richard's method above.
Thanks again. The group is so wonderful because having people like you.

Have a good holiday!

Julia

 

[RichardSchollar]

Hey Julia

I have literally learned about formulas by reading and answering (when
I can!) message board posts (of course, helps if you have a *need* for
the formula too eg at work). My personal favourite message board
(Excel related) is www.MrExcel.com/board2

Drop by and say Hi! - I lurk their under the same handle.

Best regards & have a wonderful Christmas/New Year!

Richard

 

[Don Guillett]

Your request said
" data from the format of " Sunday, Febuary 21, 2006"
if cell e1 had a properly formatted date

--
Don Guillett
SalesAid Software
(e-mail address removed)

Don,

I'm not quite sure what e1 means, but thank you anyway for looking at
my question. My problem is solved by using Richard's method above.
Thanks again. The group is so wonderful because having people like you.

Have a good holiday!

Julia

 

[Bob Phillips]

There is a detailed explanation at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)