COUNTIFS formula

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
Office Version
  1. 2019
Hi All,

what's wrong with my table's formula below and how can i amend it?
cell D47: counting "blank", "RDO" & "Request Day Off"cell, the answer expect to be: 8
cell L48: counting without "time value", "Request Day Off", "WRONG" cell, the answer expect to be: 5

thanks so much for your help

Book1
ABCDKL
47REGULAR DAY OFF136
48OTHERS DAY OFF36
51A308:45 - 18:1508:45 - 18:1500:00 - 00:00(Request Day Off)
52B412:00 - 21:3012:00 - 21:3012:00 - 21:3012:00 - 21:30
53C112:00 - 21:3012:00 - 21:3008:45 - 18:1508:45 - 18:15
54D408:45 - 18:1508:45 - 18:1512:00 - 21:3012:00 - 21:30
55E200:00 - 00:00Unpaid Leave Day 1.000:00 - 00:00Unpaid Leave Day 1.0
56F309:45 - 19:1509:45 - 19:1508:45 - 18:1508:45 - 18:15
57G612:00 - 21:3012:00 - 21:3008:45 - 18:1508:45 - 18:15
58H112:00 - 21:3012:00 - 21:30
59I408:45 - 18:1508:45 - 18:1512:00 - 21:3012:00 - 21:30
60J508:45 - 18:1508:45 - 18:15RDO
61K612:00 - 21:3012:00 - 21:3008:45 - 18:1508:45 - 18:15
62L308:45 - 18:1508:45 - 18:15
63M508:45 - 18:1508:45 - 18:15RDO
64N208:45 - 18:1508:45 - 18:1508:45 - 18:1508:45 - 18:15
65O300:00 - 00:00Vacation Leave Day 1.000:00 - 00:00Vacation Leave Day 1.0
66P112:00 - 21:3012:00 - 21:3008:45 - 18:1508:45 - 18:15
67Q512:00 - 21:3012:00 - 21:30RDO
68R7RDO12:00 - 21:3012:00 - 21:30
69S208:45 - 18:1508:45 - 18:1512:00 - 21:3012:00 - 21:30
70T308:45 - 18:1508:45 - 18:1512:00 - 21:3012:00 - 21:30
71U412:00 - 21:3012:00 - 21:3000:00 - 00:00Vacation Leave Day 1.0
72V508:45 - 18:1508:45 - 18:1500:00 - 00:00WRONG
73W600:00 - 00:00AH12 Day 1.008:45 - 18:1508:45 - 18:15
74X200:00 - 00:00Vacation Leave Day 1.0
75Y7RDO00:00 - 00:00(Request Day Off)
76Z108:45 - 18:1508:45 - 18:15
77AA611:30 - 21:0011:30 - 21:0000:00 - 00:00AH01 Day 1.0
78AB111:30 - 21:0011:30 - 21:0012:00 - 21:3012:00 - 21:30
79AC210:00 - 19:3010:00 - 19:3011:30 - 21:0011:30 - 21:00
80AD309:45 - 19:1509:45 - 19:1511:30 - 21:0011:30 - 21:00
81AE400:00 - 00:00(Request Day Off)10:00 - 19:3010:00 - 19:30
82AF509:30 - 19:0009:30 - 19:00RDO
83AG609:30 - 19:0009:30 - 19:00
RESULT-1
Cell Formulas
RangeFormula
D47, L47D47=COUNTIFS($A$51:$A$100,"*",C$51:C$100,"",D$51:D$100,"")+COUNTIFS($A$51:$A$100,"*",C$51:C$100,"00:00 - 00:00",D$51:D$100,"(request day off)")+COUNTIFS($A$51:$A$100,"*",C$51:C$100,"",D$51:D$100,"*")
D48, L48D48=COUNTIFS($A$51:$A$100,"*",C$51:C$100,"00:00 - 00:00",D$51:D$100,"*")-COUNTIFS($A$51:$A$100,"*",C$51:C$100,"00:00 - 00:00",D$51:D$100,"(request day off)")
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Hello there. I have copied and pasted your data into a blank spreadsheet, and the formula in D47 comes out as 8!!!, so don't see where the error is. Also, by applying filters to simulate your formula, the result for L48 comes out as 6, so again I can't see what's wrong (if anything). I know this won't be of much help, but I hope someone else can be of more help.
Book1
DEFGHIJKL
186
236
Sheet1
Cell Formulas
RangeFormula
D1, L1D1=COUNTIFS($A$51:$A$100,"*",C$51:C$100,"",D$51:D$100,"")+COUNTIFS($A$51:$A$100,"*",C$51:C$100,"00:00 - 00:00",D$51:D$100,"(request day off)")+COUNTIFS($A$51:$A$100,"*",C$51:C$100,"",D$51:D$100,"*")
D2, L2D2=COUNTIFS($A$51:$A$100,"*",C$51:C$100,"00:00 - 00:00",D$51:D$100,"*")-COUNTIFS($A$51:$A$100,"*",C$51:C$100,"00:00 - 00:00",D$51:D$100,"(request day off)")
 
Upvote 0
i tried as you said copy to a new sheet and it's working just find
however not functioning well under the same sheet while after macro/others formula run, so that come with wrong result

thanks in advance anyway
 
Upvote 0
Hello again. You say other macros have run - are any of them turning off automatic calculation? If so, that may be the cause.
 
Upvote 0
i run that(all) macros below row 49
so... it probably not an issue over it?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw
J
  • Solved
IFS formula with TIME
Replies
9
Views
184
Fluff
Fluff
F
  • Question
Excel Formula for Dynamic Timetable with Variable Time Increments based on certain hours of the day
Replies
1
Views
326
Muhammad_Usman
Muhammad_Usman
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
153
dilshod_k
D
M
  • Solved
Split single cell value in 2 columns.
Replies
7
Views
110
Fluff
Fluff

Forum statistics

Threads
1,214,924
Messages
6,122,294
Members
449,077
Latest member
Rkmenon

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top