Need formula to count rows with a text character in a number string, within a date range

Lila Carsten

I would like to write a formula to count how many times a text character appears in a number string, within a specific week of the year. My JOB # column contains many project numbers (63254C, 63255C, 3235M, 63256C, 3236M... etc.). I would like to know how many "C" characters I have in that column, and how many "M" characters I have in the column. I also need to count them in correspondence to a week of the year. I have this formula labeling my weeks for the date when I start a project:

IF(AND([PM Start Date]144 >= DATE(2017, 6, 5), [PM Start Date]144 <= DATE(2017, 6, 11)), "W23"

Now, I'd like to count how many rows have a "C" int he JOB # column, and also have "W23" in that row. 

Any idea how I can accomplish this?

J. Craig Williams

Lila,

1. This formula will count the number of cells in the [JOB #] column that contain one or more "C"'s

=COUNTIF([JOB #]:[JOB #], FIND("C", @cell) > 0)

The formula should not be in the [JOB #] column.

Note the "CC" will be counted once, but I think that is OK for your use-case as described.

Change C to M for the other.

2. To also take into account the week number column (I named mine [WeekNum], the formula is:

=COUNTIFS([JOB #]:[JOB #], FIND("C", @cell) > 0, WeekNum:WeekNum, "W23")

A slightly better version replaces the hard-coded W23 with a reference to a cell that contains W23 (and next week can contain W24)

=COUNTIFS([JOB #]:[JOB #], FIND("C", @cell) > 0, WeekNum:WeekNum, $Example$8)

$Example$8 is the absolute reference to a cell containing the week number.

3. Lastly, you can replace your formula determining the week number with this:

="W"+WEEKNUMBER(date) which will return the week number for a date.

Note that Smartsheet follows the ISO standard for this, so weeks start on Monday and the first week of the year has the first Thursday of the year. If you deal with US or Islamic companies, this may be off (US weeks start on Sunday and Islamic start on Saturday) 

.Or if you deal with one of my customer's customer - they had their own system that did not match any of the standard three above.

Hope this helps.

Craig

 

J. Craig Williams

Aside to Smartsheet: Help doc for WEEKNUMBER is incorrect - it says 

"Returns a number representing the week of the year, 1-52, where 1 is the first week in the year"

but it can (and should) return 53 on certain years.

Craig

Lila Carsten

Thank you Craig! This works like a charm! I have been trying to figure this one out for about a week, looking through the forum to see if anyone else has written a formula like this one. I really appreciate you help!

J. Craig Williams

You are welcome.

I'm behind on the Community due to other commitments, so glad I could help.

If you get stuck again, you might consider a support contract. Contact Richard at Smarter Business Processes (richardr@smarterbusinessprocesses.com). I take support requests before I get on the Community to answer questions. It is like having your own Smartsheet expert down the hall at work.

Cheers. 

Craig