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

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

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

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

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

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

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

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

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

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

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

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