Excel functions - making flexible "range" in COUNTIF

[nooaon]

I want to use Excel for counting the frequency of traffic occurance in
minute. My data fields contain:

A B C
1 start_time Scr_IP
(hh:mm:ss) (text)
2 12:30:10 aaa.aaa.aaa.aaa
3 12:30:16 bbb.bbb.bbb.bbb
4 12:30:50 aaa.aaa.aaa.aaa
5 12:31:11 bbb.bbb.bbb.bbb
6 12:31:21 aaa.aaa.aaa.aaa
7 12:31:35 bbb.bbb.bbb.bbb

in column C must be:
C2>> 1 >> aaa.aaa.aaa.aaa occured one time within 1 min
ago (during 12:29:10-12:30:10)
C3>> 1 >> bbb.bbb.bbb.bbb occured one time within 1 min
ago (during 12:29:16-12:30:16)
C4>> 2 >> aaa.aaa.aaa.aaa occured 2 times within 1 min
ago (during 12:29:50-12:30:50)
C5>> 2
C6>> 2
C7>> 2

i try to use =COUNTIF(range, criteria) like but the range need to b
flexible not fix as B2:B7.
In C2 the formula must be =COUNTIF(B2:B2, B2)
In C3 the formula must be =COUNTIF(B2:B3, B3)
In C4 the formula must be =COUNTIF(B2:B4, B4)
In C5 the formula must be =COUNTIF(B4:B5, B5) since 1 minute i
measured from 12:30:11 to 12:31:11
In C6 the formula must be =COUNTIF(B4:B6, B7)
In C7 the formula must be =COUNTIF(B4:B7, B7)

the problem is "range" i cannot put formula in it, can anyone help?

thank yo

 

[A.W.J. Ales]

Hi,

With your range in A2:B7 (heading in A1:C1) and with your interval ( 1
minute in your example) as input in G2 (input as 00:01:00) try :

=SUMPRODUCT(--($B$2:$B$7=B2)*($A$2:$A$7<=A2)*($A$2:$A$7>A2-$G$2))

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[nooaon]

i got it thank you very much. you save my life!!

by the way what is "--" after "SUMPRODUCT(" ?

nooao

 

[Norman Harker]

Hi nooaon!

The -- is used to coerce the TRUE and FALSE returns to 1 and 0.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[A.W.J. Ales]

YW nooan,

Thanks for your response. It's allways nice to hear that a solution was
usefull.

(Norman : Thanks for allready answering his second question).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *