Cumulative Counting in column

[antman123]

Hi All,

I have a data set that is ordered by day. Each day can have up to 4 rows. For each day the row has another column to determine if it's a high tide or low tide (simply by and H or L).

I need to create a column that for each day will count the number of high tides and low tide values.

In the example below I am looking for a way to create the count in column C.

E.G

COLUMN A COL B COL C

01/01/2015 L 1
01/01/2015 H 1
01/01/2015 L 2
01/01/2015 H 2
02/01/2015 H 1
02/01/2015 H 2
02/01/2015 L 1
02/01/2015 H 3

Any help would be much appreciated.

Anthony

 

[danzon]

assume your data starts on row 1

use this in C1

=COUNTIFS($A$1:$A1,A1,$B$1:$B1,B1)

and drag down

 

[carteBlanche]

Hi antman,

Assuming the top row is row #2:

=SUMIF($A$2:$A2;$A2;$B$2:$B2)

 

[antman123]

Thanks guys, that is amazing! If I wanted to make the scenario a bit more complicated, is this possible in Excel?

I know that there will never be more than 4 rows per day in my sheet, but there could be fewer than 4 rows per day. Knowing that, I would my count column (column C) to count the number of rows per day, then assign the count number based on the following logic:

The first H for a day will get the the number 1 assigned in column C
The second H for a day will get the number 3 assigned in column C
The first L for a day will get the number 2 assigned in column C
The second L for the day will get the number 4 assigned in column C

e.g.
E.G

COLUMN A COL B COL C

01/01/2015 L 2 (because it's the first L for the day)
01/01/2015 H 1 (because it's the first H for the day)
01/01/2015 L 4 (because it's the second L for the day)
01/01/2015 H 3 (because it's the second H for the day)
02/01/2015 H 1
02/01/2015 H 3
02/01/2015 L 2
Is this possible in Excel?

Instead of counting the number of times an H or L occurs in a particular day, I want to use the following logic:

 

[danzon]

again, assuming data starts on row 1

in C1 enter

=IF(B1="L",CHOOSE(COUNTIFS($A$1:$A1,A1,$B$1:$B1,B1),2,4),IF(B1="H",CHOOSE(COUNTIFS($A$1:$A1,A1,$B$1:$B1,B1),1,3)))

and drag down