Counting cells with formulas

Donni Matthew

My underlying issue is that I'm trying to count cells that begin with a certain number.  The data that is present in my SubCategory column is a picklist.  Examples:

• 106 COST BILLED IN ERROR
• 110 FAILURE TO PURSUE PAYMENT AFTER INVOICE SUBMISSION
• 201 CLIENT FAILED TO COMMUNICATE WITH FIRM
• 207 PER CLIENT - COST IS NOT BILLABLE

So, I'm looking to count everything that begins with "1" or "2" and so on.

My first attempt was to create another column with a formula for "left" that pulled the first digit from the SubCategory column.  However, when I do my Countif, the results are 0.  I believe the Countif is looking at the formula & not the value.

Any suggestions on how to deal with this?

Paul Newcome

Can you post your exact LEFT and exact COUNTIF formulas? It may be that your COUNTIF is looking for a numeric value, but your LEFT formula is producing a text value.

Donni Matthew

Here is the LEN formula:  =LEFT([Reason SubCategory]1, 1)

 

Here is the IFCOUNT formula:  =COUNTIF({NY Credit Authorization Range 1}, "1")

Paul Newcome

Wrap your LEFT statement in a VALUE function.

 

=VALUE(LEFT([Reason SubCategory]1, 1))

 

and remove the quotes from around the 1 in your COUNTIF.

 

=COUNTIF({NY Credit Authorization Range 1}, 1)