Formula Needed - Count

[baggarwal]

Hi All:

I have the data set like this:
Grp EnvNo EnvNo Count
1101010 1101011 BB
1101010 1101012 BB
1101010 1101013 BB
1101010 1101014 BB
1101010 1101015 BB
1101020 1101021 BB


I want to count the number of BB's at the Group Envelope ID level so I need a formula that will go into Column A and count the BB for each unique group envelope ID. So in this set I would want to see a count of 2 as oppose to 6. Can someone please tell me how this can be done.

 

[Robert Mika]

If your data are in columns A-C:
=SUM(IF(FREQUENCY(IF(C$2:C$12="BB",A$2:A$12),A$2:A$12),1))

Array formula confirm (CTR+SHIFT+ENTER)

 

[Aladin Akyurek]

In case you have text numbers as Grp...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A7<>"",
    IF(C2:C7="BB", MATCH("~"&A2:A7,A2:A7&"",0))),
      ROW(A2:A7)-ROW(A2)+1),1))

 

[T. Valko]

Hi All:

I have the data set like this:
Grp EnvNo EnvNo Count
1101010 1101011 BB
1101010 1101012 BB
1101010 1101013 BB
1101010 1101014 BB
1101010 1101015 BB
1101020 1101021 BB


I want to count the number of BB's at the Group Envelope ID level so I need a formula that will go into Column A and count the BB for each unique group envelope ID. So in this set I would want to see a count of 2 as oppose to 6. Can someone please tell me how this can be done.

One way...

Array entered**:

=SUM(IF(FREQUENCY(IF(C2:C7="BB",MATCH(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the data range of column A.