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

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.

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

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



wrote:

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.

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

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

--
Don Guillett
SalesAid Software

wrote in message
ps.com...
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.

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

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


RichardSchollar wrote:
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

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

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


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

--
Don Guillett
SalesAid Software

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

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


wrote:
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


RichardSchollar wrote:
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

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

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

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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


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

--
Don Guillett
SalesAid Software

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

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
wrote in message
ups.com...
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


RichardSchollar wrote:
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