Got a long list of data (column A), each of which has been attributed a section or subsection number (column B) which may or may not break down as far as 4 levels (1, 1.1, 1.1.1, 1.1.1.1).

Sometimes the data may apply to more than one subsection, so in column C and maybe D there is another one of these numbers.

How can I count how many rows are attributed to each overall SECTION? So for section 1 I’d want to count anything beginning with 1, whether it’s 1, 1.1.3, 1.2.1.4, etc. Is it possible to set up the COUNTIF with some sort of wildcard, like “1*”? Surely not inside the quote marks?

(No need to count 3 columns at once, I'll happily copy it across columns!)

(I’m also aware at present that 1 and 1.1 are recognised as numbers, whereas 1.1.1 etc is recognised as text – but presumably I can manually format the cells to get over that, if need be.)

Thanks as always!

Brendan

 

Set up a new column (hidden if you want) with the formular.

=LEFT(<ref>,1) (where <ref> is the cell containing the section number!)

This will extract the 1st character i.e. the main heading number.

You can then do a COUNTIF() on this column.

Al.

 

Wow! My dad's brother's called Robert!

Cheers muchly!

 

What, you mean it actually worked! I just made it up

Roberts my dad's cousin!