Count if Formula

[siobeh]

Hello,

Could you please help me with my formula. I'm trying to count the numbers of data from 9:00 PM - 4:00 AM.
I have a CountIf formula in sheet2 but still it gives me no result.

I have attached my file.
Thanks in advance!

[azumi]

Sorry wrong posting

[siobeh]

Hi Azumi,

thanks for the reply, how about the counting the data from 9:00 PM - 4:00 AM by 'Division'

[Tony Valko]

=COUNT(IF((Sheet1!$E$2:$E$45>=J$6)*(Sheet1!$E$2:$E$45>=J$6)*(Sheet1!$F$2:$F$45=$F7),1))

Array Formula, you need to press CTRL-SHIFT-ENTER button together when implemented the formula and then copied down...

I didn't look at the file but you've repeated the first criteria.

Looks like a normally entered COUNTIFS will do.

[siobeh]

Hi Tony,

can you help me with this?

[Tony Valko]

I downloaded your file and I don't understand what you're wanting to count with that.

Sheet2 is very confusing!

[siobeh]

Hi Tony,

Please see attached my original file.

I had the formula in Sheet2, what i want to accomplish is, count the the data from 9:00 PM - 4:00 AM by division.

For example for 'FDS APJ ANZ', there is a data from '3:45:52 AM', so it should count in 'J7' in Sheet2, but my formula is not working. It's the same with 'FDS APJ SEA'

[Tony Valko]

It's getting late where I'm at.

Someone will probably come by and help out. I'll check back tomorrow.

[Fletch74]

Hi Siobeh,

The problem you had was that you were calculating > 9PM and < 4AM on the same day. In decimals this translates to > 0.875 and < 0.1666. can't be done....

Try this formula;

Please Login or Register  to view this content.

Just need to change the cell reference for ASIA.

[gak67]

Because excel treats times without dates as being on the same day (day 0) there are no times that are less than 4am and greater than 9pm - ie. it's impossible.

Try this:

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl see attached file.