# SUM, IF, FREQUENCY formula count instances of sick time off [shorter formula?]

1. ## SUM, IF, FREQUENCY formula count instances of sick time off [shorter formula?]

Hello I'm just wondering if there is a more way of my formula in column NH4

I've repeated the formula to get the correct results but it's massive.

I don't know how to condense the formula down.

The formula is used to count instances of sick time off of based on the text "s" and "sh" the "rd" bridges the gap so the sickness is a single occurrence as opposed to multiple instances once you factor in rest days.

=SUM(IF(FREQUENCY(IF(B4:NF4="s",COLUMN(B4:NF4)),IF(B4:NF4<>"s",COLUMN(B4:NF4))),1))+SUM(IF(FREQUENCY(IF(B4:NF4="sh",COLUMN(B4:NF4)),IF(B4:NF4<>"sh",COLUMN(B4:NF4))),1))-SUM(IF(FREQUENCY(IF(B4:NF4="rd",COLUMN(B4:NF4)),IF(B4:NF4<>"rd",COLUMN(B4:NF4))),1))

I hope that makes sense?

Any help appreciated.

2. ## Re: SUM, IF, FREQUENCY formula count instances of sick time off [shorter formula?]

Originally Posted by paulanderson
. . . formula in . . . NH4 . . .
The formula is used to count instances of sick time off of based on the text "s" and "sh" the "rd" bridges the gap so the sickness is a single occurrence as opposed to multiple instances once you factor in rest days. . . .
You're using more than 300 columns, and you're worried about a few moderately long formulas?!

Picky: looks like your formula should end at col NC rather than col NF. That matters below.

=SUM(IF(FREQUENCY(..s..),1))+SUM(IF(FREQUENCY(..sh..),1))-SUM(IF(FREQUENCY(..rd..),1))

1st, an obvious simplification:

=SUM(IF(FREQUENCY(..s..),1),IF(FREQUENCY(..sh..),1))-SUM(IF(FREQUENCY(..rd..),1))

2nd, a minor change if you're willing to use array formulas.

=COUNT(1/FREQUENCY(..s..),1/FREQUENCY(..sh..))-COUNT(1/FREQUENCY(..rd..))

That's nice, but doesn't really cut the formula down much. Gotta say, I hadn't come across FREQUENCY bin ranges with FALSE values in them before. Interesting way to find transitions from STATE to NOT STATE. Good news is that there's another way to do that AS LONG AS the cell immediately left of the leftmost cell in the range and immediately right of the rightmost cell in the range would never contain the strings you're searching for. In that case, COUNTIFS(A4:NC4,"s",B4:ND4,"<>s") would also count transitions. Further, COUNTIFS called with arrays for even numbered arguments returns arrays. If you had s in NQ11, sh in NQ12, rd in NQ13, 1 in NR11, 1 in NR12, and -1 in NR13, try

=SUMPRODUCT(COUNTIFS(A4:NC4,\$NQ\$11:\$NQ\$13,B4:ND4,"<>"&\$NQ\$11:\$NQ\$13),\$NR\$11:\$NR\$13)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1