Reference Cells That Start With Specific Numbers

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?

Find more posts tagged with

IT & Operations

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Automation not working
I have a Smartsheet automation setup to reminder the planning to complete their sections. It is setup to remind weekly until completed box is checked. They received 2 reminders and then the reminders stopped. Any idea what could be wrong? I can't tell that anything has changed in the sheet. Thanks Freda
How to disable popups that obscure my formula?
I'm editing formulas in a Sheet Summary. However, I'm prevented from seeing the formula by an annoying "helpful" popup that obscures my view. I can't figure out how to get rid of the popup. Does anyone know?
Workflow Error: "Recipients: Edit workflow to fix recipients list"
I am trying to create a workflow and for some reason it gives me an error with the email I entered as the recipient for my Alert. It is an email that I know works, it is shared to the sheet, and the access to the sheet is unrestricted. I am perplexed at what the issue is. Anyone else get this and know what the issue is?