CountIf

[Ismael]

Hi you guys,

I having difficulties with a countif formula, you can see my doubt in the attach file.

Please take a look

thanks

best regards

Ismael

[Zack Barresse]

Hello Ismael,


Let's look at the following ...

To count all occurancces:

=COUNT($C$4:$D$96)

To count all occurances under your Max limit:

=COUNTIF($C$4:$D$96,"<"&C2+10/1440)

To count all occurances under your Min limit:

=COUNTIF($C$4:$D$96,"<"&C1+10/1440)

To count all occurances that intersect your Min/Max values:

=COUNTIF($C$4:$D$96,"<"&C2+10/1440)-COUNTIF($C$4:$D$96,"<"&C1+10/1440)

or

=SUMPRODUCT(--($C$4:$D$96<C2+10/1440),--($C$4:$D$96>C1+10/1440))

I would do something like naming the range (and unmerging the cells also!) with a name like 'rng'. This would take your caculation to ...

=SUMPRODUCT(--(rng<C2+10/1440),--(rng>C1+10/1440))

Note: we must do the "10/1440" as Excel calculates time in fractions of a day, so there are 1,440 minutes in a day.

[Ismael]

Hi firefytr,

thanks for your help,
I just now see your reply, becuase I stay out of office until now, but your ideia works very well.

So many thanks for you help,

Best regards,

Ismael