Countifs, Boolean <= & >=, Text or Value

I am looking for assistance to figure out why my formula is not working to parse out data from a column. My formula is:

=COUNTIFS(E$3:E$26,">="&LEFT(B3,4),E$3:E$26,"<="&RIGHT(B3,4))

Column B contains a list of 24 hour time frames (i.e. 0000 to 0100)
Column E contains a data list of 24 hour times (i.e. 0500, 0130, 1000, etc.)

I am trying to count the number of times a value occurs in column E within the range that is in column B. If I take out the > or < signs and just leave the = sign, a version of the formula for a specific number will work but I need it to search for a range and not a specific value.

I have attached a simplified version of my workbook (which in itself contains several tabs and hundreds of rows of data on various tabs)

Thank you in advance!!!!

Cliff Watson

I think you are mixing numbers and texts... ">=" requires a number, not text... maybe use =VALUE(LEFT(B3,4))

shg - my mistake... my first line in the sheet as you can see is actually 0001 to 0100. Looking at the scratch.xlsx file you uploaded, you changed the data type in column E so it isn't text anymore. I need to keep the data type as text because that is how it is imported from Access (copy/paste actually). When I use the IsText() function on the data, I get a true. Once I change the data type to what you set (I actually did a copy/paste of your column E to mine and then my formula worked).

djapigo - I have stuck in the function to change the text to a value when taking the time out of column B but when I use the value function to change the text of the data, I get an Excel formula error. I have tried just using the value function when pulling the time out of column B and that didn't work.

It seems the key to this problem is the data type for the actual data that I take from the Access database.

**** ignore **** shg has a really elegant solution above without the need of the array-enter

Hi Cliff,

Try this array-formula...

=SUM((VALUE($E$3:$E$26)>=--LEFT(B3,4))*(VALUE($E$3:$E$26)<=--RIGHT(B3,4)))

You must use array-enter (CTRL-SHIFT-ENTER), not just ENTER (you will see magical curly brackets {formula})

This seems to work with your original texts... I kind of combined shg's solution with the old version of array-formulas...

Let me know if this does not work...

Dennis

Both of these solutions work awesomely!

Thank you very much. I need to get to know the sumproduct function better!

Thank you again!!!

Cliff Watson