Yearly cell count of a certain value

**Hidai** · 01-10-2011, 03:17 PM

Hi,

I've got a file in which i have several years of 30 minutes daily data on which I calculated the percentage change of every 30 minutes( column H) relatively to the previous day's close price (column G). If I want to count how many times a certain percentage( e.g +1% or above) change occurred in a certain year but in different days of course how would I do that?
Attached you'll find the file.

Thx in advance,

Hidai

**NBVC** · 01-10-2011, 04:15 PM

Say you put the input percentage in K1

then in say, I2 enter:

=IF(A3=A2,"",IF(COUNTIFS(A$1:A2,A2,H$1:H2,">"&$K$1),COUNT(I$1:I1)+1,""))

This identifies the last date record of the group where one of those dates surpassed the percentage you input into K1.

copied down

Then to get the count, in a separate column:

=MAX(I:I)

**Hidai** · 01-10-2011, 04:48 PM

Thx. Is there a way to program it to count how many times did it appear in a certain year?
I need that because i have several years of data

Thx again.

**NBVC** · 01-10-2011, 04:54 PM

Assuming you have used the previous Column I helper, then say you have the input year in K2, then formula to count the unique entries per year is:

=COUNTIFS(A:A,">="&DATE(K2,1,1),I:I,">0")-COUNTIFS(A:A,">"&DATE(K2+1,1,1),I:I,">0")