|
Day of week function needed
Column A has dates in the year. Col B has customers on that date. What
formula can I use to find the total number of customers on all the Tuesdays? Average number for each Tuesday? TIA, I appreciate you folks being there. Sam Beardsley |
"Metolius Dad" wrote in message ... Column A has dates in the year. Col B has customers on that date. What formula can I use to find the total number of customers on all the Tuesdays? Average number for each Tuesday? TIA, I appreciate you folks being there. Sam Beardsley One way In Column C, enter =WEEKDAY(A1) To get the total number of customers for a Tuesday, enter =COUNTIF(A1:A23,"= 3") Of course, you need to adjust the range. /Fredrik |
Try this for total number of Tuesday customers:
=SUMPRODUCT((WEEKDAY(A1:A365)=3)*(B1:B365<"")) And try this for the average: =SUMPRODUCT((WEEKDAY(A1:A365)=3)*(B1:B365<""))/SUMPRODUCT(--(WEEKDAY(A1:A36 5)=3)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Metolius Dad" wrote in message ... Column A has dates in the year. Col B has customers on that date. What formula can I use to find the total number of customers on all the Tuesdays? Average number for each Tuesday? TIA, I appreciate you folks being there. Sam Beardsley |
Hi RD
This doesn't seem to be working. It DOES count the number of times during the year that ANY customers were in on a Tuesday but I wanted something that counted the total number of customers in on all of the Tuesdays. Thank you Sam "Ragdyer" wrote: Try this for total number of Tuesday customers: =SUMPRODUCT((WEEKDAY(A1:A365)=3)*(B1:B365<"")) And try this for the average: =SUMPRODUCT((WEEKDAY(A1:A365)=3)*(B1:B365<""))/SUMPRODUCT(--(WEEKDAY(A1:A36 5)=3)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Metolius Dad" wrote in message ... Column A has dates in the year. Col B has customers on that date. What formula can I use to find the total number of customers on all the Tuesdays? Average number for each Tuesday? TIA, I appreciate you folks being there. Sam Beardsley |
I'm sorry, but I don't understand your distinction between "ANY" customer
and "total number" of customers. Could you give an example of your datalist, where that difference would be evident? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Metolius Dad" wrote in message ... Hi RD This doesn't seem to be working. It DOES count the number of times during the year that ANY customers were in on a Tuesday but I wanted something that counted the total number of customers in on all of the Tuesdays. Thank you Sam "Ragdyer" wrote: Try this for total number of Tuesday customers: =SUMPRODUCT((WEEKDAY(A1:A365)=3)*(B1:B365<"")) And try this for the average: =SUMPRODUCT((WEEKDAY(A1:A365)=3)*(B1:B365<""))/SUMPRODUCT(--(WEEKDAY(A1:A36 5)=3)) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Metolius Dad" wrote in message ... Column A has dates in the year. Col B has customers on that date. What formula can I use to find the total number of customers on all the Tuesdays? Average number for each Tuesday? TIA, I appreciate you folks being there. Sam Beardsley |
Hi RD
Your original formula counted the number of times ANY customers came in on the Tuesdays during the year i.e. if 122 people came in on one Tuesday it still counted that occurance as one. I tweaked with the formula you gave me abit, removed the <"" and it's working fine. I haven't worked on the average formula yet. Thanks again for being there. Sam "RagDyeR" wrote: I'm sorry, but I don't understand your distinction between "ANY" customer and "total number" of customers. Could you give an example of your datalist, where that difference would be evident? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Metolius Dad" wrote in message ... Hi RD This doesn't seem to be working. It DOES count the number of times during the year that ANY customers were in on a Tuesday but I wanted something that counted the total number of customers in on all of the Tuesdays. Thank you Sam "Ragdyer" wrote: Try this for total number of Tuesday customers: =SUMPRODUCT((WEEKDAY(A1:A365)=3)*(B1:B365<"")) And try this for the average: =SUMPRODUCT((WEEKDAY(A1:A365)=3)*(B1:B365<""))/SUMPRODUCT(--(WEEKDAY(A1:A36 5)=3)) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Metolius Dad" wrote in message ... Column A has dates in the year. Col B has customers on that date. What formula can I use to find the total number of customers on all the Tuesdays? Average number for each Tuesday? TIA, I appreciate you folks being there. Sam Beardsley |
| All times are GMT +1. The time now is 09:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com