formula help

[sam__gleeson]

I have this formula looking at range D7:AH7 for the word sick..can the formula not count the weekends?

=COUNTIF($D7:$AH7,"SICK")

Cheers

 

[FryGirl]

Where are the dates to count weekdays?

 

[sam__gleeson]

Where are the dates to count weekdays?

Hi

Dates in C6:AH6

Thanks

 

[FryGirl]

Does this work for you?

=SUMPRODUCT(--($D$7:$AH$7="SICK"),--(WEEKDAY($D$6:$AH$6,3)<5))

I assumed you meant D6:AH6 not C6:AH6

 

[sam__gleeson]

Does this work for you?

=SUMPRODUCT(--($D$7:$AH$7="SICK"),--(WEEKDAY($D$6:$AH$6,3)<5))

I assumed you meant D6:AH6 not C6:AH6

Yes your correct about the range

Yes formula works great....how does the formula work please?

What are the lines hightlighted red in your formula?

Thanks

 

[FryGirl]

It's called a double unary. It is used to convert an internal array of TRUE/FALSE into 1/0

This is a good source for further explanations.

http://xldynamic.com/source/xld.SUMPRODUCT.html

The Weekday part tests the weekday of the date which ends up giving you...

{3,4,5,6,0,1,2,3,4,5,6,0,1,2,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5}<5)

Now when you check for less than 5 (Sat and Sun), the numbers turn into TRUE/FALSE which is multiplied by the first TRUE/FALSE looking for the word "SICK".

To get a better understanding of how and what a formula is doing, use the Evaluate Formula function

 

[sam__gleeson]

Thank you

 

[FryGirl]

You're welcome. Happy to help!

 

[SpreedsheetCrusader]

hi .

is this shorter ? seems to work with me: (FRI - SAT = OFFDAYS)

@ E7

[COLOR=#333333][FONT=Verdana]=SUMPRODUCT([/FONT][/COLOR][COLOR=Blue][FONT=Verdana]([COLOR=Red]WEEKDAY([COLOR=Green]E3:T3[/COLOR])<6[/COLOR])*([COLOR=Red]E4:T4="SICK"[/COLOR])[/FONT][/COLOR][COLOR=#333333][FONT=Verdana])[/FONT][/COLOR]

Excel 2010
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	F	S	SS	M	T	W	TH	F	S	SS	M	T	W	TH	F	S
3	1-Jan-16	2-Jan-16	3-Jan-16	4-Jan-16	5-Jan-16	6-Jan-16	7-Jan-16	8-Jan-16	9-Jan-16	10-Jan-16	11-Jan-16	12-Jan-16	13-Jan-16	14-Jan-16	15-Jan-16	16-Jan-16
4	SICK			SICK	SICK	SICK		SICK	SICK
5
6
7	3
Sheet1

 

[SpreedsheetCrusader]

why the "--" ? can you explain why we need the numeric conversion here ?

thanks