# Thread: formula help Thanks: 0 Likes: 0

1. ## formula help

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

Code:
`=COUNTIF(\$D7:\$AH7,"SICK")`
Cheers

2. ## Re: formula help

Where are the dates to count weekdays?

3. ## Re: formula help

Originally Posted by FryGirl
Where are the dates to count weekdays?
Hi

Dates in C6:AH6

Thanks

4. ## Re: formula help

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

5. ## Re: formula help

Originally Posted by 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

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

What are the lines hightlighted red in your formula?

Thanks

6. ## Re: formula help

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

Thank you

8. ## Re: formula help

You're welcome. Happy to help!

9. ## Re: formula help

hi .

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

@ E7
Code:
`=SUMPRODUCT((WEEKDAY(E3:T3)<6)*(E4:T4="SICK"))`

Excel 2010
EFGHIJKLMNOPQRST
2FSSSMTWTHFSSSMTWTHFS
31-Jan-162-Jan-163-Jan-164-Jan-165-Jan-166-Jan-167-Jan-168-Jan-169-Jan-1610-Jan-1611-Jan-1612-Jan-1613-Jan-1614-Jan-1615-Jan-1616-Jan-16
4SICKSICKSICKSICKSICKSICK
5
6
73

Sheet1

10. ## Re: formula help

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

thanks

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•