Reference Cells That Start With Specific Numbers

ajcooper35

I would like to write a COUNTIFS statement that will count the number of cells that begin with certain numbers in a column. 

The goal for this is to be able to count cells that are between certain times of day, so I would like it to be able count the cells that begin with "1:, 2:, 3:, 4:, 5:" and a separate one for other times.

Is this possible to do?

Brian W

You could use the LEFT function which returns the leftmost characters in a string. Also, COUNTIFS returns results that match all criteria, so I think you would need to use COUNTIF with OR.

=COUNTIF([Column Name]:[Column Name], OR(LEFT(@cell, 2) = "1:", LEFT(@cell, 2) = "2:", LEFT(@cell, 2) = "3:", LEFT(@cell, 2) = "4:", LEFT(@cell, 2) = "5:”))

ajcooper35

I just tried it and it isn't working. Does this work with cross referencing sheets?

One thing I forgot to mention (the reason why I was looking for COUNTIFS is because it is going to reference one column for the time, and a second column for AM or PM. 

Under the Column 'Morning', I want a cell that will reference a column on another sheet for all cells that begin with 6:, 7: , 8:, 9:, 10:, 11:, AND have AM in another column.

 

 

Brian W

In that case you should be able to use COUNTIFS. And you should be able to use Reference Another Sheet when you are entering your formula. Your formula should end up looking something like this:

=COUNTIFS({Other Sheet Range 1}, OR(LEFT(@cell, 2) = "1:", LEFT(@cell, 2) = "2:", LEFT(@cell, 2) = "3:", LEFT(@cell, 2) = "4:", LEFT(@cell, 2) = "5:"), {Other Sheet Range 2}, "AM”)

{Other Sheet Range 1} will be the column in the other sheet with the times and {Other Sheet Range 2} will be the column with AM or PM.

ajcooper35

Thank you very much, just tried this and it worked. I appreciate the help