Multiple answer for particular timesets and making a box if all relevant data matches

D

DylantheD

New Member
Joined
Sep 30, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi all wonderful Legends!

I need some help with a form I'm trying to create. The basics are below.
If the time is between 06:00 and 14:00 a box on the end needs to read DAY, or if between 14:00 and 22:00 ARVO, and 22:00 to 06:00 NIGHT.

Then, if the Completed column shows no, i want another box which i have started to work with, using =COUNTIFS(K:K,"AD1",M:M,"DAY") but this only works as i have only added in manually the ones with a no.
If, and only if the completed is a no, then a counter that determines how many no's for each shift, and warehouse.

then a little box on the side, shows the results of each shift in each warehouse that was a no completed.

like this one:

Thanks for all the help, i know its probably something simple i'm missing, but anytime i try to add another if inside an if i get error codes.
AD1-DAY
8​
OD3-DAY
36​
AD1-ARVO
13​
OD3-ARVO
10​
AD1-NIGHT
16​
OD3-NIGHT
13​

CustomerDockDocketdatetimeItemDescriptionPalletQtyW/HouseTypeSHIFTCompleted Yes/No
B053622311515323/09/2024
1:02​
BCCK10581
13317777​
Coke Classic 250ml
22​
od325CI006338DayNo
B98710
23115154​
23/09/2024
6:19​
BSGM00143BSHPCANBig Sky Hazy Pale 4.3%
0​
AD125CI006343DAYNo
B00684
23115155​
23/09/2024
6:41​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006344Yes
B053622311515623/09/2024
6:48​
BCCK10581
13317777​
Coke Classic 250ml
22​
od325CI006345DAYNo
B511042311515723/09/2024
6:48​
BREK00370PK0940Sodaly Lem L Bitters
5​
od325CI006346Yes
B511282311515823/09/2024
6:51​
BBDT00480BBDT00480Bobby Lemon Lime
2​
AD125CI006347Yes
B510312311515923/09/2024
7:49​
BHCS00022BHCS00022Harbour Master 4.4%
3​
od325CI006350DAYNo
B006842311516023/09/2024
8:14​
BCUS90200
284606​
Carlton Dry 3.5%
11​
AD125CI006353Yes
B511042311516123/09/2024
8:26​
BREK00370PK0940Sodaly Lem L Bitters
6​
od325CI006354Yes
B511412311516223/09/2024
8:51​
BFRK10170
12009147​
V Green AU
18​
od325CI006355Yes
B984882311516323/09/2024
9:11​
BCPS80058
228102​
Wild Turkey & Cola 6.5%
18​
od325CI006357Yes
B053622311516423/09/2024
9:21​
BKKS26107
13315145​
Kirks Lemonade SugarFree
3​
AD125CI006359DAYNo
B984882311516523/09/2024
9:33​
BCPS80011
228089​
Wild Turkey 4.8%
22​
AD125CI006360Yes
B51104
23115166​
23/09/2024
10:15​
BREK00120PK0960Rasp Lade 250ml AU/NZ v1
14​
od325CI006362DAYNo
B006842311516723/09/2024
10:22​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006363
B053622311516823/09/2024
10:25​
BCCK10581
13317777​
Coke Classic 250ml
22​
od325CI006364DAYNo
B984882311516923/09/2024
10:47​
BCPS80058
228102​
Wild Turkey & Cola 6.5%
18​
od325CI006366DAYNo
B006842311517023/09/2024
10:58​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006367Yes
B006842311517123/09/2024
11:45​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006370Yes
B511042311517223/09/2024
11:50​
BREK00090PK0953Ginger Lemon 250 AU/NZv1
12​
od325CI006372DAYNo
B511042311517323/09/2024
11:54​
BREK00090PK0953Ginger Lemon 250 AU/NZv1
8​
od325CI006373DAYNo
B053622311517423/09/2024
12:50​
BCCK10581
13317777​
Coke Classic 250ml
22​
od325CI006376Yes
B000202311517523/09/2024
12:51​
BMDT00750BMDT00750Fizzer Summer Pash 4.0%
6​
od325CI006377DAYNo
B053622311517623/09/2024
12:58​
BCCS56018
13317572​
Sprite
22​
AD125CI006378Yes
B006842311517723/09/2024
13:35​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006384Yes
B006842311517823/09/2024
14:27​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006388Yes
B511402311517923/09/2024
15:32​
BHBS00280BHBS00280Jetty Road Lager 4.2%
5​
od325CI006393Yes
B053622311518023/09/2024
15:38​
BKKS26107
13315145​
Kirks Lemonade SugarFree
22​
AD125CI006394Yes
B984882311518123/09/2024
15:44​
BCPS80207
231743​
Nelson County 4.6%
15​
od325CI006395Yes
B680102311518223/09/2024
16:22​
BBEV055202ENDV2DRT202 SILS SILT
1​
od325CI006399Yes
B985102311518323/09/2024
16:33​
BCBS00440PKGCANLBPALLorry Boys Pale Ale 4.2%
4​
od325CI006400Yes
B006842311518423/09/2024
16:51​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006401Yes
B511042311518523/09/2024
17:18​
BREK00381PK1011Sodaly Guava AU/NZ v2
11​
od325CI006406Yes
B053622311518623/09/2024
17:44​
BCCS56018
13317572​
Sprite
22​
AD125CI006407Yes
B006842311518723/09/2024
19:31​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006409Yes
B006842311518823/09/2024
20:10​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006410Yes
B053622311518923/09/2024
21:48​
BKKS26107
13315145​
Kirks Lemonade SugarFree
22​
AD125CI006412Yes
B053622311519023/09/2024
21:49​
BCCK10581
13317777​
Coke Classic 250ml
22​
od325CI006413ARVONo
B984882311519123/09/2024
22:23​
BCPS80011
228089​
Wild Turkey 4.8%
2​
od325CI006415NIGHTNo
B006842311519223/09/2024
22:25​
BCUS42080
283164​
VB 4.9%
20​
AD125CI006416NIGHTNo
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Is this what you're looking for?

Book1
ABCDEFGHIJKLMN
1
2
3
4
5DAYAD12OD38
6ARVOAD10OD31
7NIGHTAD11OD32
8
9
10CustomerDockDocketdatetimeItemDescriptionPalletQtyW/HouseTypeSHIFTCompleted Yes/No
11
12B053622311515323/09/20241:02:00 AMBCCK1058113317777Coke Classic 250ml22od325CI006338NIGHTNo
13B987102311515423/09/20246:19:00 AMBSGM00143BSHPCANBig Sky Hazy Pale 4.3%0AD125CI006343DAYNo
14B006842311515523/09/20246:41:00 AMBCUS42080283164VB 4.9%20AD125CI006344 Yes
15B053622311515623/09/20246:48:00 AMBCCK1058113317777Coke Classic 250ml22od325CI006345DAYNo
16B511042311515723/09/20246:48:00 AMBREK00370PK0940Sodaly Lem L Bitters5od325CI006346 Yes
17B511282311515823/09/20246:51:00 AMBBDT00480BBDT00480Bobby Lemon Lime2AD125CI006347 Yes
18B510312311515923/09/20247:49:00 AMBHCS00022BHCS00022Harbour Master 4.4%3od325CI006350DAYNo
19B006842311516023/09/20248:14:00 AMBCUS90200284606Carlton Dry 3.5%11AD125CI006353 Yes
20B511042311516123/09/20248:26:00 AMBREK00370PK0940Sodaly Lem L Bitters6od325CI006354 Yes
21B511412311516223/09/20248:51:00 AMBFRK1017012009147V Green AU18od325CI006355 Yes
22B984882311516323/09/20249:11:00 AMBCPS80058228102Wild Turkey & Cola 6.5%18od325CI006357 Yes
23B053622311516423/09/20249:21:00 AMBKKS2610713315145Kirks Lemonade SugarFree3AD125CI006359DAYNo
24B984882311516523/09/20249:33:00 AMBCPS80011228089Wild Turkey 4.8%22AD125CI006360 Yes
25B511042311516623/09/202410:15:00 AMBREK00120PK0960Rasp Lade 250ml AU/NZ v114od325CI006362DAYNo
26B006842311516723/09/202410:22:00 AMBCUS42080283164VB 4.9%20AD125CI006363 
27B053622311516823/09/202410:25:00 AMBCCK1058113317777Coke Classic 250ml22od325CI006364DAYNo
28B984882311516923/09/202410:47:00 AMBCPS80058228102Wild Turkey & Cola 6.5%18od325CI006366DAYNo
29B006842311517023/09/202410:58:00 AMBCUS42080283164VB 4.9%20AD125CI006367 Yes
30B006842311517123/09/202411:45:00 AMBCUS42080283164VB 4.9%20AD125CI006370 Yes
31B511042311517223/09/202411:50:00 AMBREK00090PK0953Ginger Lemon 250 AU/NZv112od325CI006372DAYNo
32B511042311517323/09/202411:54:00 AMBREK00090PK0953Ginger Lemon 250 AU/NZv18od325CI006373DAYNo
33B053622311517423/09/202412:50:00 PMBCCK1058113317777Coke Classic 250ml22od325CI006376 Yes
34B000202311517523/09/202412:51:00 PMBMDT00750BMDT00750Fizzer Summer Pash 4.0%6od325CI006377DAYNo
35B053622311517623/09/202412:58:00 PMBCCS5601813317572Sprite22AD125CI006378 Yes
36B006842311517723/09/20241:35:00 PMBCUS42080283164VB 4.9%20AD125CI006384 Yes
37B006842311517823/09/20242:27:00 PMBCUS42080283164VB 4.9%20AD125CI006388 Yes
38B511402311517923/09/20243:32:00 PMBHBS00280BHBS00280Jetty Road Lager 4.2%5od325CI006393 Yes
39B053622311518023/09/20243:38:00 PMBKKS2610713315145Kirks Lemonade SugarFree22AD125CI006394 Yes
40B984882311518123/09/20243:44:00 PMBCPS80207231743Nelson County 4.6%15od325CI006395 Yes
41B680102311518223/09/20244:22:00 PMBBEV055202ENDV2DRT202 SILS SILT1od325CI006399 Yes
42B985102311518323/09/20244:33:00 PMBCBS00440PKGCANLBPALLorry Boys Pale Ale 4.2%4od325CI006400 Yes
43B006842311518423/09/20244:51:00 PMBCUS42080283164VB 4.9%20AD125CI006401 Yes
44B511042311518523/09/20245:18:00 PMBREK00381PK1011Sodaly Guava AU/NZ v211od325CI006406 Yes
45B053622311518623/09/20245:44:00 PMBCCS5601813317572Sprite22AD125CI006407 Yes
46B006842311518723/09/20247:31:00 PMBCUS42080283164VB 4.9%20AD125CI006409 Yes
47B006842311518823/09/20248:10:00 PMBCUS42080283164VB 4.9%20AD125CI006410 Yes
48B053622311518923/09/20249:48:00 PMBKKS2610713315145Kirks Lemonade SugarFree22AD125CI006412 Yes
49B053622311519023/09/20249:49:00 PMBCCK1058113317777Coke Classic 250ml22od325CI006413ARVONo
50B984882311519123/09/202410:23:00 PMBCPS80011228089Wild Turkey 4.8%2od325CI006415NIGHTNo
51B006842311519223/09/202410:25:00 PMBCUS42080283164VB 4.9%20AD125CI006416NIGHTNo
Sheet2
Cell Formulas
RangeFormula
D5:D7,F5:F7D5=COUNTIFS($K$12:$K$51,C5,$M$12:$M$51,$B5)
M12:M51M12=LET( cond1,AND($F12>=TIMEVALUE("06:00"),$F12<TIMEVALUE("14:00"),$N12="No"), cond2,AND($F12>=TIMEVALUE("14:00"),$F12<TIMEVALUE("22:00"),$N12="No"), cond3,OR(AND($F12>=TIMEVALUE("22:00"),$F12<=TIMEVALUE("23:59:59"),$N12="No"),AND($F12>=TIMEVALUE("00:00"),$F12<TIMEVALUE("06:00"),$N12="No")), IF(cond1,"DAY",IF(cond2,"ARVO",IF(cond3,"NIGHT",""))))
 
Upvote 0
For the Day, Avo, Night column this might be another option.

Excel Formula: 
=IF(N12="No",
    IFS($F12<TIMEVALUE("06:00"),"NIGHT",
        $F12<TIMEVALUE("14:00"),"DAY",
        $F12<TIMEVALUE("22:00"),"ARVO",
        TRUE,"NIGHT"),
  "")
 
Upvote 0
Solution
Guys, wondering if you could help me a little bit more. I want to have a separate count for all loads of either 20 or 22 pallets.
if you see the example, i only want the NO loads counted if they are 20 pallets or over.
See image...
Hope im explaining ok!

pallets screen.png
 
Upvote 0
You can add another criteria to the COUNTIFS formulas in the table in columns S and U.
Excel Formula: 
=COUNTIFS(($L$12:$L$51,R5,$O$12:$O$51,$Q5,$I$12:$I$51,">=20")

You will need to adjust the row numbers to suit in the formula as they're chopped off in the picture.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Compare Multiple rows and columns for potential clashes
Replies
2
Views
316
ShruggedTundra
S

Forum statistics

Threads
1,224,812
Messages
6,181,084
Members
453,021
Latest member
Justyna P

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