Count If works selectively

[altrudel]

The Goal: To total the amount of days off and requested days off in a schedule.
The Problem -
COUNTIF is working great to tabulate the requests off:
=COUNTIF(B3:H15,"OFF-R")
and boom! It counts all the requests off perfectly!

however

when i use the same function in the next cell over but replace "OFF-R" with just "OFF" it comes back with nothing.
Could it have something to do with the text formatting?
I just do not understand why it works just fine for one string of text, but fails for the other string of text. I've tried various wildcards, COUNTIFS, reformatting of text...I just can't figure it out.
3 minutes away from mental meltdown.

 

[jasonb75]

Welcome to the board!

Do any of the cells in the range actually contain the text "OFF" ?

Note that "OFF-R" will not be counted as it's not exactly the same as the criteria.

Also check the range of the second formula, depending on how you copied it you may find that it's looking at C3:I15 instead , so entries in column B will be missed.

 

[altrudel]

Thank you for the quick reply!!!
i confirmed that there are 9 cells in the range that contain the text "OFF" or "OFF " (space after the letters). I wonder if excel is reading those as something different, though?
I verified that its counting in the correct range.
After digging through all the blogs, I saw that "ctrl+shift+enter" can help function work properly, so I attempted doing that instead of simply "entering". and now i'm totally baffled because it counted 1 of the 9 when I did that.
I have tried changing the cells that say "OFF" so they say something totally different to see if that would work. No go. I have tried to change the formatting so that they are text and general. Nothing. I have tried changing the format of the sheet that each cell is referencing. Still nothing.
This was my latest attempt that counted 1 of the "OFF" items.
=COUNTIFS(B3:H15,"OFF ", B3:H15,"OFF ")
I event tried replacing "off" in the formual with a cell reference (and in that cell i put 'off') but still no go.

 

[altrudel]

=COUNTIF(B3:H15,""=D1) (with d1 = "off")
a fail

=COUNTIF(B3:H15,D1) (with d1= off ) - space after "off"
counts 1 in first row- fails other 8

=COUNTIF(B3:H15,D1) (with d1=off)
a fail

 

[jasonb75]

Trying to clarify things you mentioned above, and answer your original question.

Shift Ctrl Enter is used for array formula, with a non-array formula that can produce erratic results.

With a criteria "OFF", any cells containing "OFF " would not be counted.

Countifs would look for both "OFF" and "OFF " (2 different criteria) in the same cell, this will always result in 0.

You could use a wildcard, =COUNTIF(B3:H15,"OFF*") but this would also count "OFF-R", etc, which I assume should be ignored as they have already been counted.

That leaves adding up several countif results, either the long way

=COUNTIF(B3:H15,"OFF")+COUNTIF(B3:H15,"OFF ")

Or using an array constant

=SUM(COUNTIF(B3:H15,{"OFF","OFF "}))

Note that this method has to be nested in SUM() or it will only return the result of the first criteria.

Hope that helps.

 

[barry houdini]

If it's an actual space at the end (or even more than one space) you could also use TRIM, i.e.

=SUMPRODUCT((TRIM(B3:H15)="OFF")+0)

 

[altrudel]

YAY!!!!!!
the trim function worked perfectly!!!!!!

Thank you! Thank you! thank you! thank you!!!
Y'all ROCK!!!