| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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")) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Formula - Count Ticks | Excel Discussion (Misc queries) | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
| COUNT with Multiple Criteria Error | Excel Worksheet Functions | |||
| Count error frequency in huge column | Excel Worksheet Functions | |||
Hybrid Mode
