Search code examples
excelcountgroupingunique

Special Grouping Count IF - Excel

I have a log like that

Let me explain this in details, I have a document with some type of serial (doc#) each document may have more than one revision (Rev#) depending on its code (code).

If a Rev# gets code A or B (for simplicity just make it B) then the document is considered B no matter what the previous revisions are, i.e. the code of the last revision is what determines the document code.

What I need is a counter that counts B,C or out (there are another codes but this is for simplicity) like what is displayed on the right small table then counts them from there. It's worth to mention that I am using tables so merging is not possible and the merge in the picture is for the sake of illustration.

I know it's simple to be done using VBA but I am asking to do this using formula and - if possible - without helper table (could add extra column to the table if necessary).

Solution

  • Since you are fine with adding a column, add a column that tests for unique ids in column doc#. For the example file in the question, this code should work: 

    =IF(A1=A2,"",C1) [for cell D1, copied through D12]

    A simple COUNTIF in D should then do the job.

    Here's what the final file looks like. enter image description here