Special count.,,
 

Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

Calrify the question. It is way too unclear

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Flemming" wrote in message
...
Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

Yes this can be done using Countif Function.

In your example you have stated that Z11:Z42 contains weekday as a number
like 1, 3, 5 but near to that numbers there is some wordings within the
brackets as "(single day of illness)" & "(not at work it the weekend)". Just
confirm whether this content is also pressent on the same cell or it is in
some other column.

If the Z Column consist data look like this "3 (single day of
illness)" then use the below formula.
=COUNTIF(Z:Z,"*(single day of illness)*")

In the above Example I have referred whole column as range. For your query
you can set the formula to look the Z11:Z42 range only by using the below
function.
=COUNTIF($Z$11:$Z$42,"*(single day of illness)*")


If the Z Column consist data look like this "(single day of illness)" then
use this formula
=COUNTIF(Z:Z,"(single day of illness)")
Or
=COUNTIF($Z$11:$Z$42,"(single day of illness)")

Go through the below link for more information about using the countif
Function
http://www.contextures.com/xlFunctions04.html

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

Hi

No the content "(single day of illness)" is not pressent.

I can without any larger issues create extra formula in column AA that could
verify if it's a single day of illness or not... I was hoping to skip this
formula, but may that's not possible.

/Flemming



"Ms-Exl-Learner" wrote in message
...
Yes this can be done using Countif Function.

In your example you have stated that Z11:Z42 contains weekday as a number
like 1, 3, 5 but near to that numbers there is some wordings within the
brackets as "(single day of illness)" & "(not at work it the weekend)".
Just
confirm whether this content is also pressent on the same cell or it is in
some other column.

If the Z Column consist data look like this "3 (single day of
illness)" then use the below formula.
=COUNTIF(Z:Z,"*(single day of illness)*")

In the above Example I have referred whole column as range. For your
query
you can set the formula to look the Z11:Z42 range only by using the below
function.
=COUNTIF($Z$11:$Z$42,"*(single day of illness)*")


If the Z Column consist data look like this "(single day of illness)" then
use this formula
=COUNTIF(Z:Z,"(single day of illness)")
Or
=COUNTIF($Z$11:$Z$42,"(single day of illness)")

Go through the below link for more information about using the countif
Function
http://www.contextures.com/xlFunctions04.html

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

I agree with Ashish Mathur, things are not clear. Ms-Exl-Learner's solution
will work IF you actually have the phrases in a column, but if those are not
there, then we need more information:
Why would the entries at rows 15 and 18 not be included in the count? How
do we know they're not a single day of illness?

"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

It could be because it cannot be done.

See my response to Ms-Exl-Learner

I will to make the calculation only using the data in colomn Y or Z or a
combination....

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I might need to create formula in column AA to evaluate data before I can
get the number I'm after, but I was hoping to avoid that

Is it clear enough now?

/Flemming



"JLatham" wrote in message
...
I agree with Ashish Mathur, things are not clear. Ms-Exl-Learner's
solution
will work IF you actually have the phrases in a column, but if those are
not
there, then we need more information:
Why would the entries at rows 15 and 18 not be included in the count? How
do we know they're not a single day of illness?

"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

If you could eliminate the entries for the weekends (days off I assume?)
then this would be relatively easy.

Here's your data with the weekends removed:

.............Y.........Z
11...FALSE
12...TRUE......2
13...FALSE
14...FALSE
15...TRUE......5
16...TRUE......1
17...FALSE
18...TRUE......3
19...FALSE
20...FALSE
21...TRUE......5
22...FALSE

Array entered** :

=SUM(IF(FREQUENCY(IF(Z11:Z22<"",ROW(Z11:Z22)),IF( Z11:Z22="",ROW(Z11:Z22)))=1,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
It could be because it cannot be done.

See my response to Ms-Exl-Learner

I will to make the calculation only using the data in colomn Y or Z or a
combination....

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I might need to create formula in column AA to evaluate data before I can
get the number I'm after, but I was hoping to avoid that

Is it clear enough now?

/Flemming



"JLatham" wrote in message
...
I agree with Ashish Mathur, things are not clear. Ms-Exl-Learner's
solution
will work IF you actually have the phrases in a column, but if those are
not
there, then we need more information:
Why would the entries at rows 15 and 18 not be included in the count?
How
do we know they're not a single day of illness?

"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

Thanks Biff

I cannot remove the weekends, since extra work happens now and then...

I guess I will have to make something in column AA to determin for each
entry - that can be done.

Thanks for you time all
Flemming



"T. Valko" wrote in message
...
If you could eliminate the entries for the weekends (days off I assume?)
then this would be relatively easy.

Here's your data with the weekends removed:

............Y.........Z
11...FALSE
12...TRUE......2
13...FALSE
14...FALSE
15...TRUE......5
16...TRUE......1
17...FALSE
18...TRUE......3
19...FALSE
20...FALSE
21...TRUE......5
22...FALSE

Array entered** :

=SUM(IF(FREQUENCY(IF(Z11:Z22<"",ROW(Z11:Z22)),IF( Z11:Z22="",ROW(Z11:Z22)))=1,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
It could be because it cannot be done.

See my response to Ms-Exl-Learner

I will to make the calculation only using the data in colomn Y or Z or a
combination....

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I might need to create formula in column AA to evaluate data before I can
get the number I'm after, but I was hoping to avoid that

Is it clear enough now?

/Flemming



"JLatham" wrote in message
...
I agree with Ashish Mathur, things are not clear. Ms-Exl-Learner's
solution
will work IF you actually have the phrases in a column, but if those are
not
there, then we need more information:
Why would the entries at rows 15 and 18 not be included in the count?
How
do we know they're not a single day of illness?

"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

Maybe I could change the formula in column Z so that weekends will be marked
with 6 and 7 if both Friday and Monday is TRUE

That should solve it togeather with the Array frequency thing...

Thanks
Flemming



"T. Valko" wrote in message
...
If you could eliminate the entries for the weekends (days off I assume?)
then this would be relatively easy.

Here's your data with the weekends removed:

............Y.........Z
11...FALSE
12...TRUE......2
13...FALSE
14...FALSE
15...TRUE......5
16...TRUE......1
17...FALSE
18...TRUE......3
19...FALSE
20...FALSE
21...TRUE......5
22...FALSE

Array entered** :

=SUM(IF(FREQUENCY(IF(Z11:Z22<"",ROW(Z11:Z22)),IF( Z11:Z22="",ROW(Z11:Z22)))=1,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
It could be because it cannot be done.

See my response to Ms-Exl-Learner

I will to make the calculation only using the data in colomn Y or Z or a
combination....

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I might need to create formula in column AA to evaluate data before I can
get the number I'm after, but I was hoping to avoid that

Is it clear enough now?

/Flemming



"JLatham" wrote in message
...
I agree with Ashish Mathur, things are not clear. Ms-Exl-Learner's
solution
will work IF you actually have the phrases in a column, but if those are
not
there, then we need more information:
Why would the entries at rows 15 and 18 not be included in the count?
How
do we know they're not a single day of illness?

"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

I don't think that'll work. The formula is looking for "non-contiguous"
entries, an empty before or an empty cell after an entry.

Doing what you suggest would miss Fridays:

TRUE...Friday...5
FALSE...Saturday...6

And Mondays:

FALSE...Sunday...7
TRUE...Monday...1

Let me see if I can come up with something. No guarantees, though. This is a
tough one to crack!

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
Maybe I could change the formula in column Z so that weekends will be
marked with 6 and 7 if both Friday and Monday is TRUE

That should solve it togeather with the Array frequency thing...

Thanks
Flemming



"T. Valko" wrote in message
...
If you could eliminate the entries for the weekends (days off I assume?)
then this would be relatively easy.

Here's your data with the weekends removed:

............Y.........Z
11...FALSE
12...TRUE......2
13...FALSE
14...FALSE
15...TRUE......5
16...TRUE......1
17...FALSE
18...TRUE......3
19...FALSE
20...FALSE
21...TRUE......5
22...FALSE

Array entered** :

=SUM(IF(FREQUENCY(IF(Z11:Z22<"",ROW(Z11:Z22)),IF( Z11:Z22="",ROW(Z11:Z22)))=1,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
It could be because it cannot be done.

See my response to Ms-Exl-Learner

I will to make the calculation only using the data in colomn Y or Z or a
combination....

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I might need to create formula in column AA to evaluate data before I
can get the number I'm after, but I was hoping to avoid that

Is it clear enough now?

/Flemming



"JLatham" wrote in message
...
I agree with Ashish Mathur, things are not clear. Ms-Exl-Learner's
solution
will work IF you actually have the phrases in a column, but if those
are not
there, then we need more information:
Why would the entries at rows 15 and 18 not be included in the count?
How
do we know they're not a single day of illness?

"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

It will work cause the idea is that if there is illness on both friday and
monday then it is the same illness and not to seperate single-day-illness...

So this will ensure that both Friday and Monday will not be counted with as
single-day-illness
TRUE...Friday...5
FALSE...Saturday...6
FALSE...Sunday...7
TRUE...Monday...1

Formula for weekend days must evaluate both Friday and Monday illness -
formula is already made


TRUE...Friday...5
FALSE...Saturday...
FALSE...Sunday...
FALSE...Monday...

TRUE...Friday...
FALSE...Saturday...
FALSE...Sunday...
TRUE...Monday...1

It will work later tonight or this weekend.

It time to be with the kids now - one hour to their bedtime (Denmark).

Thanks for you time - I'm on my way to find and solve the next issue :-)
Flemming



"T. Valko" wrote in message
...
I don't think that'll work. The formula is looking for "non-contiguous"
entries, an empty before or an empty cell after an entry.

Doing what you suggest would miss Fridays:

TRUE...Friday...5
FALSE...Saturday...6

And Mondays:

FALSE...Sunday...7
TRUE...Monday...1

Let me see if I can come up with something. No guarantees, though. This is
a tough one to crack!

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
Maybe I could change the formula in column Z so that weekends will be
marked with 6 and 7 if both Friday and Monday is TRUE

That should solve it togeather with the Array frequency thing...

Thanks
Flemming



"T. Valko" wrote in message
...
If you could eliminate the entries for the weekends (days off I assume?)
then this would be relatively easy.

Here's your data with the weekends removed:

............Y.........Z
11...FALSE
12...TRUE......2
13...FALSE
14...FALSE
15...TRUE......5
16...TRUE......1
17...FALSE
18...TRUE......3
19...FALSE
20...FALSE
21...TRUE......5
22...FALSE

Array entered** :

=SUM(IF(FREQUENCY(IF(Z11:Z22<"",ROW(Z11:Z22)),IF( Z11:Z22="",ROW(Z11:Z22)))=1,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
It could be because it cannot be done.

See my response to Ms-Exl-Learner

I will to make the calculation only using the data in colomn Y or Z or
a combination....

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I might need to create formula in column AA to evaluate data before I
can get the number I'm after, but I was hoping to avoid that

Is it clear enough now?

/Flemming



"JLatham" wrote in message
...
I agree with Ashish Mathur, things are not clear. Ms-Exl-Learner's
solution
will work IF you actually have the phrases in a column, but if those
are not
there, then we need more information:
Why would the entries at rows 15 and 18 not be included in the count?
How
do we know they're not a single day of illness?

"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given
date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday
only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

Hello Flemming,

=SUMPRODUCT(--(Y11:Y26),--(Y10:Y25<Y11:Y26),--(Y12:Y27<Y11:Y26),SIGN
((Z11:Z26<5)+(Z11:Z26=5)*(Z14:Z29<1)),SIGN((Z11: Z26<1)+(Z11:Z26=1)*
(Z8:Z23<5)))

Please notice that you do not need the SIGN function here but I prefer
to play it safe by using it:
http://sulprobil.com/html/sumproduct.html

Regards,
Bernd

Special count.,,
 

Hi Bernd

This looks special... and interesting :-)

My Z-range goes from 11:42
I cannot see through how this the numbers should be then...

How should it look with the SIGN and how without?

Can you help with that?

Thanks
Flemming


"Bernd P" wrote in message
...
Hello Flemming,

=SUMPRODUCT(--(Y11:Y26),--(Y10:Y25<Y11:Y26),--(Y12:Y27<Y11:Y26),SIGN
((Z11:Z26<5)+(Z11:Z26=5)*(Z14:Z29<1)),SIGN((Z11: Z26<1)+(Z11:Z26=1)*
(Z8:Z23<5)))

Please notice that you do not need the SIGN function here but I prefer
to play it safe by using it:
http://sulprobil.com/html/sumproduct.html

Regards,
Bernd

Special count.,,
 

It will work...

Ok, I'll take your word for it. But here's my interpretation...

If you "mark" Sat and Sun and a person misses the single day Fri then:

Fri...5
Sat...6
Sun...7

There isn't an empty cell after Fri...5.

If a person misses the single day Mon then:

Sat...6
Sun...4
Mon...1

There isn't an empty cell before Mon...1

As I said, the formula I suggested is dependent upon there being an empty
cell before and after an entry (of course, this doesn't apply to the very
first or very last cell in the range).

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
It will work cause the idea is that if there is illness on both friday and
monday then it is the same illness and not to seperate
single-day-illness...

So this will ensure that both Friday and Monday will not be counted with
as single-day-illness
TRUE...Friday...5
FALSE...Saturday...6
FALSE...Sunday...7
TRUE...Monday...1

Formula for weekend days must evaluate both Friday and Monday illness -
formula is already made


TRUE...Friday...5
FALSE...Saturday...
FALSE...Sunday...
FALSE...Monday...

TRUE...Friday...
FALSE...Saturday...
FALSE...Sunday...
TRUE...Monday...1

It will work later tonight or this weekend.

It time to be with the kids now - one hour to their bedtime (Denmark).

Thanks for you time - I'm on my way to find and solve the next issue :-)
Flemming



"T. Valko" wrote in message
...
I don't think that'll work. The formula is looking for "non-contiguous"
entries, an empty before or an empty cell after an entry.

Doing what you suggest would miss Fridays:

TRUE...Friday...5
FALSE...Saturday...6

And Mondays:

FALSE...Sunday...7
TRUE...Monday...1

Let me see if I can come up with something. No guarantees, though. This
is a tough one to crack!

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
Maybe I could change the formula in column Z so that weekends will be
marked with 6 and 7 if both Friday and Monday is TRUE

That should solve it togeather with the Array frequency thing...

Thanks
Flemming



"T. Valko" wrote in message
...
If you could eliminate the entries for the weekends (days off I
assume?) then this would be relatively easy.

Here's your data with the weekends removed:

............Y.........Z
11...FALSE
12...TRUE......2
13...FALSE
14...FALSE
15...TRUE......5
16...TRUE......1
17...FALSE
18...TRUE......3
19...FALSE
20...FALSE
21...TRUE......5
22...FALSE

Array entered** :

=SUM(IF(FREQUENCY(IF(Z11:Z22<"",ROW(Z11:Z22)),IF( Z11:Z22="",ROW(Z11:Z22)))=1,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
It could be because it cannot be done.

See my response to Ms-Exl-Learner

I will to make the calculation only using the data in colomn Y or Z or
a combination....

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday only
showing numbers if ill.

I might need to create formula in column AA to evaluate data before I
can get the number I'm after, but I was hoping to avoid that

Is it clear enough now?

/Flemming



"JLatham" wrote in message
...
I agree with Ashish Mathur, things are not clear. Ms-Exl-Learner's
solution
will work IF you actually have the phrases in a column, but if those
are not
there, then we need more information:
Why would the entries at rows 15 and 18 not be included in the count?
How
do we know they're not a single day of illness?

"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given
date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday
only
showing numbers if ill.

I would like know who many times an illness is a single day only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

Thanks for you input - and do see Brend P's formula


"T. Valko" wrote in message
...
It will work...

Ok, I'll take your word for it. But here's my interpretation...

If you "mark" Sat and Sun and a person misses the single day Fri then:

Fri...5
Sat...6
Sun...7

There isn't an empty cell after Fri...5.

If a person misses the single day Mon then:

Sat...6
Sun...4
Mon...1

There isn't an empty cell before Mon...1

As I said, the formula I suggested is dependent upon there being an empty
cell before and after an entry (of course, this doesn't apply to the very
first or very last cell in the range).

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
It will work cause the idea is that if there is illness on both friday
and monday then it is the same illness and not to seperate
single-day-illness...

So this will ensure that both Friday and Monday will not be counted with
as single-day-illness
TRUE...Friday...5
FALSE...Saturday...6
FALSE...Sunday...7
TRUE...Monday...1

Formula for weekend days must evaluate both Friday and Monday illness -
formula is already made


TRUE...Friday...5
FALSE...Saturday...
FALSE...Sunday...
FALSE...Monday...

TRUE...Friday...
FALSE...Saturday...
FALSE...Sunday...
TRUE...Monday...1

It will work later tonight or this weekend.

It time to be with the kids now - one hour to their bedtime (Denmark).

Thanks for you time - I'm on my way to find and solve the next issue :-)
Flemming



"T. Valko" wrote in message
...
I don't think that'll work. The formula is looking for "non-contiguous"
entries, an empty before or an empty cell after an entry.

Doing what you suggest would miss Fridays:

TRUE...Friday...5
FALSE...Saturday...6

And Mondays:

FALSE...Sunday...7
TRUE...Monday...1

Let me see if I can come up with something. No guarantees, though. This
is a tough one to crack!

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
Maybe I could change the formula in column Z so that weekends will be
marked with 6 and 7 if both Friday and Monday is TRUE

That should solve it togeather with the Array frequency thing...

Thanks
Flemming



"T. Valko" wrote in message
...
If you could eliminate the entries for the weekends (days off I
assume?) then this would be relatively easy.

Here's your data with the weekends removed:

............Y.........Z
11...FALSE
12...TRUE......2
13...FALSE
14...FALSE
15...TRUE......5
16...TRUE......1
17...FALSE
18...TRUE......3
19...FALSE
20...FALSE
21...TRUE......5
22...FALSE

Array entered** :

=SUM(IF(FREQUENCY(IF(Z11:Z22<"",ROW(Z11:Z22)),IF( Z11:Z22="",ROW(Z11:Z22)))=1,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Flemming" wrote in message
...
It could be because it cannot be done.

See my response to Ms-Exl-Learner

I will to make the calculation only using the data in colomn Y or Z
or a combination....

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday
only showing numbers if ill.

I might need to create formula in column AA to evaluate data before I
can get the number I'm after, but I was hoping to avoid that

Is it clear enough now?

/Flemming



"JLatham" wrote in message
...
I agree with Ashish Mathur, things are not clear. Ms-Exl-Learner's
solution
will work IF you actually have the phrases in a column, but if those
are not
there, then we need more information:
Why would the entries at rows 15 and 18 not be included in the
count? How
do we know they're not a single day of illness?

"Flemming" wrote:

Hi all,

I have column that tells me if a person have been ill on a given
date.

Column Y (Y11:Y42) contains TRUE if ill and FALSE if on work.
Column Z (Z11:Z42) contains weekday as a number 1=monday 5=friday
only
showing numbers if ill.

I would like know who many times an illness is a single day
only...!

Y Z
11 FALSE
12 TRUE 2 (single day of illness)
13 FALSE
14 FALSE
15 TRUE 5
16 FALSE (not at work it the weekend)
17 FALSE (not at work it the weekend)
18 TRUE 1
19 FALSE
20 TRUE 3 (single day of illness)
21 FALSE
22 FALSE
23 TRUE 5 (single day of illness)
24 FALSE (not at work it the weekend)
25 FALSE (not at work it the weekend)
26 FALSE

Result should then be 3 times

Can this be done without using VBA?

Thanks,
Flemming

Special count.,,
 

Hello Flemming,

Take
=SUMPRODUCT(--(Y11:Y42),--(Y10:Y41<Y11:Y42),--(Y12:Y43<Y11:Y42),SIGN
((Z11:Z42<5)+(Z11:Z42=5)*(Z14:Z45<1)),SIGN((Z11: Z42<1)+(Z11:Z42=1)*
(Z8:Z39<5)))
then.

Since the used/checked OR criteria do not overlap (meaning: no more
than one of them can be true), you could also write:
=SUMPRODUCT(--(Y11:Y42),--(Y10:Y41<Y11:Y42),--(Y12:Y43<Y11:Y42),
(Z11:Z42<5)+(Z11:Z42=5)*(Z14:Z45<1),(Z11:Z42<1) +(Z11:Z42=1)*
(Z8:Z39<5))

Please notice that this formula looks over and above your defined
input area of rows 11:42. Please make sure that you do not have any
"disturbing" values in Y11, Y43, Z8:Z11 and Z43:Z45 (Y11 and Y43
should not contain boolean values, Z8:Z11 should not show 5s, Z43:Z45
no 1s).

Regards,
Bernd

Special count.,,
 

Thanks for you time Bernd - looking forward to test this.

But now I out of the door and on my way to a weekend of golf with my friends
:)

/Flemming


"Bernd P" wrote in message
...
Hello Flemming,

Take
=SUMPRODUCT(--(Y11:Y42),--(Y10:Y41<Y11:Y42),--(Y12:Y43<Y11:Y42),SIGN
((Z11:Z42<5)+(Z11:Z42=5)*(Z14:Z45<1)),SIGN((Z11: Z42<1)+(Z11:Z42=1)*
(Z8:Z39<5)))
then.

Since the used/checked OR criteria do not overlap (meaning: no more
than one of them can be true), you could also write:
=SUMPRODUCT(--(Y11:Y42),--(Y10:Y41<Y11:Y42),--(Y12:Y43<Y11:Y42),
(Z11:Z42<5)+(Z11:Z42=5)*(Z14:Z45<1),(Z11:Z42<1) +(Z11:Z42=1)*
(Z8:Z39<5))

Please notice that this formula looks over and above your defined
input area of rows 11:42. Please make sure that you do not have any
"disturbing" values in Y11, Y43, Z8:Z11 and Z43:Z45 (Y11 and Y43
should not contain boolean values, Z8:Z11 should not show 5s, Z43:Z45
no 1s).

Regards,
Bernd

Special count.,,
 

Hi Brend

The first formula using SIGN will my Excel sheet not accept

The second formula is counting 1 day to few - sometimes

Thank you for your time - I think I'm going in another direction.


I'll make the extra column with extra formulars in.

Thanks all,
Flemming



"Bernd P" wrote in message
...
Hello Flemming,

Take
=SUMPRODUCT(--(Y11:Y42),--(Y10:Y41<Y11:Y42),--(Y12:Y43<Y11:Y42),SIGN
((Z11:Z42<5)+(Z11:Z42=5)*(Z14:Z45<1)),SIGN((Z11: Z42<1)+(Z11:Z42=1)*
(Z8:Z39<5)))
then.

Since the used/checked OR criteria do not overlap (meaning: no more
than one of them can be true), you could also write:
=SUMPRODUCT(--(Y11:Y42),--(Y10:Y41<Y11:Y42),--(Y12:Y43<Y11:Y42),
(Z11:Z42<5)+(Z11:Z42=5)*(Z14:Z45<1),(Z11:Z42<1) +(Z11:Z42=1)*
(Z8:Z39<5))

Please notice that this formula looks over and above your defined
input area of rows 11:42. Please make sure that you do not have any
"disturbing" values in Y11, Y43, Z8:Z11 and Z43:Z45 (Y11 and Y43
should not contain boolean values, Z8:Z11 should not show 5s, Z43:Z45
no 1s).

Regards,
Bernd