Count if formula error
 

A B C D E F
1 Name Classification In Out Hours
2 DELL, BRYAN Pipe Fitter 06:10 18:11 12.01
3 WRITE, ALLAN Pipe Fitter 06:01 16:47 10.77
4 DAVIES, ALAN Quality Control 05:39 13:42 8.05
5 COLLINS, TAY Rigger Advanced 06:03 18:14 12.11
6 PORT, RICHARD Rigger Advanced 05:47 16:50 11.05

In the above example, I can use the formula =COUNTIF(E2:E6,"<=18*:*00")
to count the total number of staff clocking out after 6pm. But when I try
to use the formulas below to count the number of 'Pipe Fitters' clocking out
after 6pm it won't work - what am I doing wrong?

=COUNTIF((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))
=COUNT(IF((B2:B7="Pipe Fitter")*(E2:E7,"<=18*:*00")))
=SUMPRODUCT((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))

Count if formula error
 

=SUMPRODUCT(--(B2:B6="Pipe Fitter),--(E2:E60=--"18:00"))

will count after and 18:00 included


=SUMPRODUCT(--(B2:B6="Pipe Fitter),--(E2:E60<--"18:00"))

will count earlier than 18:00

--


Regards,


Peo Sjoblom

"Kate83" wrote in message
...
A B C D E F
1 Name Classification In Out Hours
2 DELL, BRYAN Pipe Fitter 06:10 18:11 12.01
3 WRITE, ALLAN Pipe Fitter 06:01 16:47 10.77
4 DAVIES, ALAN Quality Control 05:39 13:42 8.05
5 COLLINS, TAY Rigger Advanced 06:03 18:14 12.11
6 PORT, RICHARD Rigger Advanced 05:47 16:50 11.05

In the above example, I can use the formula =COUNTIF(E2:E6,"<=18*:*00")
to count the total number of staff clocking out after 6pm. But when I try
to use the formulas below to count the number of 'Pipe Fitters' clocking
out
after 6pm it won't work - what am I doing wrong?

=COUNTIF((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))
=COUNT(IF((B2:B7="Pipe Fitter")*(E2:E7,"<=18*:*00")))
=SUMPRODUCT((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))

Count if formula error
 

Is it possible to clockout after midnight?

Try this:

=SUMPRODUCT(--(B2:B6="Pipe Fitter"),--(HOUR(E2:E6)=18))

--
Biff
Microsoft Excel MVP


"Kate83" wrote in message
...
A B C D E F
1 Name Classification In Out Hours
2 DELL, BRYAN Pipe Fitter 06:10 18:11 12.01
3 WRITE, ALLAN Pipe Fitter 06:01 16:47 10.77
4 DAVIES, ALAN Quality Control 05:39 13:42 8.05
5 COLLINS, TAY Rigger Advanced 06:03 18:14 12.11
6 PORT, RICHARD Rigger Advanced 05:47 16:50 11.05

In the above example, I can use the formula =COUNTIF(E2:E6,"<=18*:*00")
to count the total number of staff clocking out after 6pm. But when I try
to use the formulas below to count the number of 'Pipe Fitters' clocking
out
after 6pm it won't work - what am I doing wrong?

=COUNTIF((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))
=COUNT(IF((B2:B7="Pipe Fitter")*(E2:E7,"<=18*:*00")))
=SUMPRODUCT((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))

Count if formula error
 

Thank you - you have made my day :)

"T. Valko" wrote:

Is it possible to clockout after midnight?

Try this:

=SUMPRODUCT(--(B2:B6="Pipe Fitter"),--(HOUR(E2:E6)=18))

--
Biff
Microsoft Excel MVP


"Kate83" wrote in message
...
A B C D E F
1 Name Classification In Out Hours
2 DELL, BRYAN Pipe Fitter 06:10 18:11 12.01
3 WRITE, ALLAN Pipe Fitter 06:01 16:47 10.77
4 DAVIES, ALAN Quality Control 05:39 13:42 8.05
5 COLLINS, TAY Rigger Advanced 06:03 18:14 12.11
6 PORT, RICHARD Rigger Advanced 05:47 16:50 11.05

In the above example, I can use the formula =COUNTIF(E2:E6,"<=18*:*00")
to count the total number of staff clocking out after 6pm. But when I try
to use the formulas below to count the number of 'Pipe Fitters' clocking
out
after 6pm it won't work - what am I doing wrong?

=COUNTIF((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))
=COUNT(IF((B2:B7="Pipe Fitter")*(E2:E7,"<=18*:*00")))
=SUMPRODUCT((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))

Count if formula error
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Kate83" wrote in message
...
Thank you - you have made my day :)

"T. Valko" wrote:

Is it possible to clockout after midnight?

Try this:

=SUMPRODUCT(--(B2:B6="Pipe Fitter"),--(HOUR(E2:E6)=18))

--
Biff
Microsoft Excel MVP


"Kate83" wrote in message
...
A B C D E F
1 Name Classification In Out Hours
2 DELL, BRYAN Pipe Fitter 06:10 18:11 12.01
3 WRITE, ALLAN Pipe Fitter 06:01 16:47 10.77
4 DAVIES, ALAN Quality Control 05:39 13:42 8.05
5 COLLINS, TAY Rigger Advanced 06:03 18:14 12.11
6 PORT, RICHARD Rigger Advanced 05:47 16:50 11.05

In the above example, I can use the formula =COUNTIF(E2:E6,"<=18*:*00")
to count the total number of staff clocking out after 6pm. But when I
try
to use the formulas below to count the number of 'Pipe Fitters'
clocking
out
after 6pm it won't work - what am I doing wrong?

=COUNTIF((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))
=COUNT(IF((B2:B7="Pipe Fitter")*(E2:E7,"<=18*:*00")))
=SUMPRODUCT((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))

Count if formula error
 

Hi,

Since 18 hours is 18/24 parts of a day, which in turn is 0.75 you can
simplify your formula to

=SUMPRODUCT(--(B2:B6="Pipe Fitter"),--(E2:E6=18/24))
or
=SUMPRODUCT(--(B2:B6="Pipe Fitter"),--(E2:E6=.75))
and if you enter Pipe Fitters in D1
=SUMPRODUCT(--(B2:B6=D1),--(E2:E6=.75))

Cheers,
Shane Devenshire


"Kate83" wrote:

A B C D E F
1 Name Classification In Out Hours
2 DELL, BRYAN Pipe Fitter 06:10 18:11 12.01
3 WRITE, ALLAN Pipe Fitter 06:01 16:47 10.77
4 DAVIES, ALAN Quality Control 05:39 13:42 8.05
5 COLLINS, TAY Rigger Advanced 06:03 18:14 12.11
6 PORT, RICHARD Rigger Advanced 05:47 16:50 11.05

In the above example, I can use the formula =COUNTIF(E2:E6,"<=18*:*00")
to count the total number of staff clocking out after 6pm. But when I try
to use the formulas below to count the number of 'Pipe Fitters' clocking out
after 6pm it won't work - what am I doing wrong?

=COUNTIF((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))
=COUNT(IF((B2:B7="Pipe Fitter")*(E2:E7,"<=18*:*00")))
=SUMPRODUCT((B2:B6="Pipe Fitter")*(E2:E6,"<=18*:*00"))