Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
Hello,
can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
If the only numbers in that column are dates, you can use:
=SUMPRODUCT(--(WEEKDAY(A1:A999)=1)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Marcel Marien wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
Try something like this:
=SUMPRODUCT((A1:A500)*(WEEKDAY(A1:A50)1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
Actually, if the range of cells may contain dates, text, blanks, or errors....
Try this ARRAY FORMULA: =SUM(IF(ISNUMBER(WEEKDAY(A1:A50-1)),--(WEEKDAY(A1:A50,2)<7))) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try something like this: =SUMPRODUCT((A1:A500)*(WEEKDAY(A1:A50)1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
Dear Dave,
thank you *sooo* much for the links - they were very helpful reading. I guess I need something like =SUMPRODUCT(--(CELL("Format";A1:V1)="D1")*(WEEKDAY(A1:V1)1)) However, the 1st part of the formula seems to return the cell format of the 1st cell of the range, not the format of each cell. Do you have any idea what I am doing wrong? Marcel "Dave Peterson" schrieb im Newsbeitrag ... If the only numbers in that column are dates, you can use: =SUMPRODUCT(--(WEEKDAY(A1:A999)=1)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Marcel Marien wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
Nothing. That's the way =cell() works.
Marcel Marien wrote: Dear Dave, thank you *sooo* much for the links - they were very helpful reading. I guess I need something like =SUMPRODUCT(--(CELL("Format";A1:V1)="D1")*(WEEKDAY(A1:V1)1)) However, the 1st part of the formula seems to return the cell format of the 1st cell of the range, not the format of each cell. Do you have any idea what I am doing wrong? Marcel "Dave Peterson" schrieb im Newsbeitrag ... If the only numbers in that column are dates, you can use: =SUMPRODUCT(--(WEEKDAY(A1:A999)=1)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Marcel Marien wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
Marcel
If you can give a sampling of the kinds of values the cells would contain, we can probably tailor a solution that meets your needs. For instance, if the only numeric values will be either dates after Jan 01, 1990 or department numbers which are between 100 and 999, that scenario can be accommodated. *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Dear Ron, Thank you for your suggestion. You check in it whether the cell contains a number, but I need to check whether it contains a date, for example by asking =CELL("Format";A1)="D1". If, however, I don't refer to a singel cell, but to a range of cells (=CELL("Format";A1:V1)="D1") the formular returns only the formating value of the 1st cell in the range. - Do you know any alternative or do you know what I am doing wrong? Marcel "Ron Coderre" schrieb im Newsbeitrag ... Actually, if the range of cells may contain dates, text, blanks, or errors.... Try this ARRAY FORMULA: =SUM(IF(ISNUMBER(WEEKDAY(A1:A50-1)),--(WEEKDAY(A1:A50,2)<7))) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try something like this: =SUMPRODUCT((A1:A500)*(WEEKDAY(A1:A50)1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
IOW, CELL doesn't work on arrays.
Biff "Dave Peterson" wrote in message ... Nothing. That's the way =cell() works. Marcel Marien wrote: Dear Dave, thank you *sooo* much for the links - they were very helpful reading. I guess I need something like =SUMPRODUCT(--(CELL("Format";A1:V1)="D1")*(WEEKDAY(A1:V1)1)) However, the 1st part of the formula seems to return the cell format of the 1st cell of the range, not the format of each cell. Do you have any idea what I am doing wrong? Marcel "Dave Peterson" schrieb im Newsbeitrag ... If the only numbers in that column are dates, you can use: =SUMPRODUCT(--(WEEKDAY(A1:A999)=1)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Marcel Marien wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
Hello Ron,
here is a sample of how the data looks like now. The red numbers represent the months and days of another calender. They reach from 1-19. 17 01.01.1900 18 18 02.01.1900 18 19 03.01.1900 18 Sunday Monday Tuesday I have added an extra row, so that I can use the weekday line to check for a Sunday by writing: =SUMPRODUCT(--(DayOfWeek<"Sunday");(--(DayOfWeek<0))) but I am not totally happy with that solution, since I would like to have the option of checking for several days at once (by using <=) and I have found no formula to turn the spelled out weekdays back into their corresponding numbers. Marcel "Ron Coderre" schrieb im Newsbeitrag ... Marcel If you can give a sampling of the kinds of values the cells would contain, we can probably tailor a solution that meets your needs. For instance, if the only numeric values will be either dates after Jan 01, 1990 or department numbers which are between 100 and 999, that scenario can be accommodated. *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Dear Ron, Thank you for your suggestion. You check in it whether the cell contains a number, but I need to check whether it contains a date, for example by asking =CELL("Format";A1)="D1". If, however, I don't refer to a singel cell, but to a range of cells (=CELL("Format";A1:V1)="D1") the formular returns only the formating value of the 1st cell in the range. - Do you know any alternative or do you know what I am doing wrong? Marcel "Ron Coderre" schrieb im Newsbeitrag ... Actually, if the range of cells may contain dates, text, blanks, or errors.... Try this ARRAY FORMULA: =SUM(IF(ISNUMBER(WEEKDAY(A1:A50-1)),--(WEEKDAY(A1:A50,2)<7))) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try something like this: =SUMPRODUCT((A1:A500)*(WEEKDAY(A1:A50)1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
In , Marcel Marien
spake thusly: here is a sample of how the data looks like now. The red numbers represent the months and days of another calender. They reach from 1-19. 17 01.01.1900 18 18 02.01.1900 18 19 03.01.1900 18 Sunday Monday Tuesday Uh. "Red"? I am reading this in a standard Usenet newsreader. There is no HTML, and there is no color. This is a text-only medium. (I am reading this under NetBSD in Unix, albeit over an ANSI terminal emulator on my Windowx XP box.) I have added an extra row, so that I can use the weekday line to check for a Sunday by writing: =SUMPRODUCT(--(DayOfWeek<"Sunday");(--(DayOfWeek<0))) but I am not totally happy with that solution, since I would like to have the option of checking for several days at once (by using <=) and I have found no formula to turn the spelled out weekdays back into their corresponding numbers. Can't you use either the WEEKDAY function or a LOOKUP/CHOOSE approach? In fact, it looks like Ron suggested exactly that when he wrote (also reproduced in full down below for all you top-poster lovers): =SUM(IF(ISNUMBER(WEEKDAY(A1:A50-1)),--(WEEKDAY(A1:A50,2)<7))) (Array formula.) -dman- ================================================== = "Ron Coderre" schrieb im Newsbeitrag ... Marcel If you can give a sampling of the kinds of values the cells would contain, we can probably tailor a solution that meets your needs. For instance, if the only numeric values will be either dates after Jan 01, 1990 or department numbers which are between 100 and 999, that scenario can be accommodated. *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Dear Ron, Thank you for your suggestion. You check in it whether the cell contains a number, but I need to check whether it contains a date, for example by asking =CELL("Format";A1)="D1". If, however, I don't refer to a singel cell, but to a range of cells (=CELL("Format";A1:V1)="D1") the formular returns only the formating value of the 1st cell in the range. - Do you know any alternative or do you know what I am doing wrong? Marcel "Ron Coderre" schrieb im Newsbeitrag ... Actually, if the range of cells may contain dates, text, blanks, or errors.... Try this ARRAY FORMULA: =SUM(IF(ISNUMBER(WEEKDAY(A1:A50-1)),--(WEEKDAY(A1:A50,2)<7))) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try something like this: =SUMPRODUCT((A1:A500)*(WEEKDAY(A1:A50)1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
Marcel:
Thank you for posting the structure, but....it is VERY important that we understand the actual values that you expect. Here's an example of how that will be helpful: If the dates will be later than 31-DEC-1980 and the other numeric values will be less than 10,000 then we can assume that values greater than 29,586 (Excel's date serial number for 31-DEC-1980) are dates. Are the values you posted representative of the actual values? Or do we need some more details? *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Hello Ron, here is a sample of how the data looks like now. The red numbers represent the months and days of another calender. They reach from 1-19. 17 01.01.1900 18 18 02.01.1900 18 19 03.01.1900 18 Sunday Monday Tuesday I have added an extra row, so that I can use the weekday line to check for a Sunday by writing: =SUMPRODUCT(--(DayOfWeek<"Sunday");(--(DayOfWeek<0))) but I am not totally happy with that solution, since I would like to have the option of checking for several days at once (by using <=) and I have found no formula to turn the spelled out weekdays back into their corresponding numbers. Marcel "Ron Coderre" schrieb im Newsbeitrag ... Marcel If you can give a sampling of the kinds of values the cells would contain, we can probably tailor a solution that meets your needs. For instance, if the only numeric values will be either dates after Jan 01, 1990 or department numbers which are between 100 and 999, that scenario can be accommodated. *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Dear Ron, Thank you for your suggestion. You check in it whether the cell contains a number, but I need to check whether it contains a date, for example by asking =CELL("Format";A1)="D1". If, however, I don't refer to a singel cell, but to a range of cells (=CELL("Format";A1:V1)="D1") the formular returns only the formating value of the 1st cell in the range. - Do you know any alternative or do you know what I am doing wrong? Marcel "Ron Coderre" schrieb im Newsbeitrag ... Actually, if the range of cells may contain dates, text, blanks, or errors.... Try this ARRAY FORMULA: =SUM(IF(ISNUMBER(WEEKDAY(A1:A50-1)),--(WEEKDAY(A1:A50,2)<7))) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try something like this: =SUMPRODUCT((A1:A500)*(WEEKDAY(A1:A50)1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count cells that contain a date that is not a Sunday
Marcel,
CELL() is the most reliable way to check for a date format, so I suggest you use it in your formula. Since it does not work in arrays you might be better off using an auxiliary column with CELL("format";A2) and then base your SUMPRODUCT on this column also. HTH Kostis Vezerides Marcel Marien wrote: Hello Ron, here is a sample of how the data looks like now. The red numbers represent the months and days of another calender. They reach from 1-19. 17 01.01.1900 18 18 02.01.1900 18 19 03.01.1900 18 Sunday Monday Tuesday I have added an extra row, so that I can use the weekday line to check for a Sunday by writing: =SUMPRODUCT(--(DayOfWeek<"Sunday");(--(DayOfWeek<0))) but I am not totally happy with that solution, since I would like to have the option of checking for several days at once (by using <=) and I have found no formula to turn the spelled out weekdays back into their corresponding numbers. Marcel "Ron Coderre" schrieb im Newsbeitrag ... Marcel If you can give a sampling of the kinds of values the cells would contain, we can probably tailor a solution that meets your needs. For instance, if the only numeric values will be either dates after Jan 01, 1990 or department numbers which are between 100 and 999, that scenario can be accommodated. *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Dear Ron, Thank you for your suggestion. You check in it whether the cell contains a number, but I need to check whether it contains a date, for example by asking =CELL("Format";A1)="D1". If, however, I don't refer to a singel cell, but to a range of cells (=CELL("Format";A1:V1)="D1") the formular returns only the formating value of the 1st cell in the range. - Do you know any alternative or do you know what I am doing wrong? Marcel "Ron Coderre" schrieb im Newsbeitrag ... Actually, if the range of cells may contain dates, text, blanks, or errors.... Try this ARRAY FORMULA: =SUM(IF(ISNUMBER(WEEKDAY(A1:A50-1)),--(WEEKDAY(A1:A50,2)<7))) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try something like this: =SUMPRODUCT((A1:A500)*(WEEKDAY(A1:A50)1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Marcel Marien" wrote: Hello, can anybody tell me how to formulate: "Count all cells in a row which contain a date (not every cell contains a date) that does not fall on a Sunday"? Somehow I just don't seem to get it. Thank you very much in advance, Marcel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date in merged cells | Excel Discussion (Misc queries) | |||
problem with date stamp and protected cells | Excel Worksheet Functions | |||
Look up date on another sheet and do count of active cells (column | Excel Worksheet Functions | |||
UDF is updateing cells on another sheet with count from current sheet. | Excel Discussion (Misc queries) | |||
Minimum Date Range from other cells | Excel Worksheet Functions |