Count cells that contain a date that is not a Sunday

[Marcel Marien]

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]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Ron Coderre]

Try something like this:

=SUMPRODUCT((A1:A50>0)*(WEEKDAY(A1:A50)>1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Coderre]

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

 

[Marcel Marien]

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

 

[Marcel Marien]

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

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

Try something like this:

=SUMPRODUCT((A1:A50>0)*(WEEKDAY(A1:A50)>1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Dave Peterson]

Nothing. That's the way =cell() works.

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

 

[Ron Coderre]

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

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

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

Try something like this:

=SUMPRODUCT((A1:A50>0)*(WEEKDAY(A1:A50)>1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

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

 

[Biff]

IOW, CELL doesn't work on arrays.

Biff

Nothing. That's the way =cell() works.

 

[Marcel Marien]

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

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

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

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


:

Try something like this:

=SUMPRODUCT((A1:A50>0)*(WEEKDAY(A1:A50)>1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

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

 

[Dallman Ross]

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-

===================================================

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

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

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


:

Try something like this:

=SUMPRODUCT((A1:A50>0)*(WEEKDAY(A1:A50)>1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

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

 

[Ron Coderre]

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

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

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

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

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


:

Try something like this:

=SUMPRODUCT((A1:A50>0)*(WEEKDAY(A1:A50)>1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

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

 

[vezerid]

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

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

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

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

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


:

Try something like this:

=SUMPRODUCT((A1:A50>0)*(WEEKDAY(A1:A50)>1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

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