CODEWITHSUNDEEP
excelgroupingcounter
user918967
user918967

Reputation: 2167

How do I create a counter in excel for groups?

I am really befuddled as to how I create a counter that increments by 1 every time it finds Type=A (there are only two Types: A and B). I want to use this later as a grouping variable so I can sum the duration of each of the events.

My spreadsheet only has columns A, B, and C and I need a formula for Column D (Event) 

A                   B            C                 D
DateTime            Type      Duration          Event
23Nov1973  0700      A                            1
24Nov1973  0700      A            1               1
25Nov1973  0700      A            1               1
25Nov1973  1800      B  
26Nov1973  0700      B  
26Nov1973  1300      A            0.25            2
26Nov1973  1800      B  
27Nov1973  0700      B  
27Nov1973  1300      B  
27Nov1973  1800      A            0.208           3
28Nov1973  0700      A            0.541           3
28Nov1973  1300      A            0.25            3
28Nov1973  1800      B            
29Nov1973  0700      A            0.541           4
29Nov1973  1300      A            0.25            4

Upvotes: 1

Views: 156

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

If your Type data starts from B2 cell, then:

  1. write in D2 formula: =IF(B2="B","",1)
  2. write in D3 following formula: =IF(B3="B","",IF(D2="",MAX($D$2:D2)+1,D2)) and drag it down

Upvotes: 1

Related Questions