Smart sheet summary formula is needed.

Mark Stacey

I'm trying to create a formula in the summary section of a report that will count every cell in the column that starts with specific letters.

Kelly Moore

@Mark Stacey

A report does not directly allow that filtering capability. You will need to add a helper column (a checkbox will do) to your sheet to make this determination. You can then count this helper column for your results.

In the checkbox helper column add this formula. It will count each row. You will need to edit the below formula with your actual column name and the actual number of letters you are evaluating.

=IF(LEFT([your text column]@cell, the number of letters to count)="whatever you are evaluating", 1)

For example, if I am looking for the first letters to be 'ab' - which means 2 letters, and my field name is 'text' then my formula would look like this:

=IF(LEFT(text@row, 2)="ab", 1)

Does this work for you?

Kelly

Mark Stacey

Thanks Kelly! Please see below. When I create the check box column (NES) and put in the formula I get '#UNPARSEABLE'.

Kelly Moore

Hey @Mark Stacey

I can't see any reason the screenshot formula in your NES column is returning unparseable. Please copy paste the formula below into your sheet just to be sure.

=IF(LEFT([Pole Owner]@row, 3) = "NES", 1)

Your summary field should be

=COUNTIFS(NES:NES,1)

Does these work for you?

Kelly

Mark Stacey

Hi Kelly,

That works! Thank you so much for your help! The difference was '@row' instead of '@cell'.

Thanks again,

Mark