Special count.,,

[Flemming]

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

 

[Ashish Mathur]

Calrify the question. It is way too unclear

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ms-Exl-Learner]

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!

 

[Flemming]

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

 

[JLatham]

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]

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

 

[T. Valko]

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.

 

[Flemming]

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

 

[Flemming]

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]

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!

 

[Flemming]

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

 

[Bernd P]

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

 

[Flemming]

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

 

[T. Valko]

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).

 

[Flemming]

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

 

[Bernd P]

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

 

[Flemming]

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

 

[Flemming]

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