Count a number of values if certain criteria in another column is met

Hi all,

I would rally appreciate some help before I lose my mind!! I have tried to get my head around the SUM, SUMIF, COUNTIF and LOOKUP options, and every variation of the same to achieve an outcome, but I have failed.

I have a contractual document set out in excel. Columns run as follows; Clause number, Clause M or blank, then clause is either C, NC or U. I want to be able to the following;

- count the number of clauses
- count the number of M's (and therefore the number of blanks)
- of the number of Ms, how many are C, NC or U's.
- then if I update a clause from a C to an NC, I was the above count to update also.

Thank you so much in advance of any help you can provide.

Thanks Sam...sample file attached.

In the example, to try and reiterate, certain clauses are mandatory, and therefore are marked with an M in column D. You will also see that in column G, there is an option for the clause to be either C, NC, PC, U or R.

I need to be able to;

- count the number of clauses in column C (each cell with text would represent a clause)
- count the number of mandatory clauses (indicated as mandatory with an M in column D)
- of these mandatory clauses, how many are then C, NC, PC, U or R. (And then the same for the non-mandatory ones).
- Then I would like these numbers to update automatically as the clauses change from NC or C for example.

I hope this makes sense...and I appreciate you taking to the time respond and help.

Ideally, the text in blue would not be counted, as they are headings of sections of the document. If they can be excluded from the count that would be awesome!

And then yes, an individual count of all M's which are C's...all M's which are NC's etc.

Any blank row (not got an M in column D), is non-mandatory and another count as above is needed.

Awesome...works like a charm...thank you so much!

I have incorporated things into the master document and it is soooo good. Excel never ceases to amaze me...but here is one for you...

Is there anyway of taking a whole row, and populating a new worksheet/table with that row based on certain criteria. For example, using the file I have uploaded above;

- If in column A, I listed each clause with its owner; "Finance", "Commercial", "Legal", "Mandatory"
- Then if one of those clauses was marked as "NC", the whole row would copy across and populate a new table in the relevant worksheet for the owner.

If this was possible, Finance would only have to look at one worksheet and see what was NC for them etc...

Or am I asking too much??