Apologies in advance if this is a no brainer .. I am brain dead atm...
I have a SS with 3 columns of dates based on stages The assumption is if there is a date in one column and not another that it is still in that phase.
How can I combine 2 countifs to get the data needed below?
I need to combine two counts ifs
I need to get counts for L5, M5, N5, O5, P5 based off of the countifs in K5 and then the same for rows 5 - 8
Here is a sample table:
Thank you in advance for any help!
I have a SS with 3 columns of dates based on stages The assumption is if there is a date in one column and not another that it is still in that phase.
How can I combine 2 countifs to get the data needed below?
I need to combine two counts ifs
I need to get counts for L5, M5, N5, O5, P5 based off of the countifs in K5 and then the same for rows 5 - 8
Here is a sample table:
countifs-issues.xlsx | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
1 | 7/26/2023 | 6/25/2023 | 4/26/2023 | 1/26/2023 | 7/26/2022 | ||||||||||||||||
2 | 6/26/2023 | 4/27/2023 | 1/27/2023 | 7/26/2022 | 1/1/1993 | ||||||||||||||||
3 | Count | < 30 days | 31 - 90 Days | 91 days - 180 days | 181 - 365 days | 1 year + | Check | ||||||||||||||
4 | Inventory (all stages) | 50 | 4 | 4 | 15 | 9 | 18 | 50 | |||||||||||||
5 | Stage 2 (active) | 13 | 0 | ||||||||||||||||||
6 | Placed Stage 3 | 2 | 0 | ||||||||||||||||||
7 | Stage 2 (stage / no stage 3) | 12 | 0 | ||||||||||||||||||
8 | Stage 3 | 23 | 0 | ||||||||||||||||||
9 | Check2 | 50 | 0 | 0 | 0 | 0 | 0 | ||||||||||||||
10 | Account | Desk | Stage1 | Stage2 | Stage3 | ||||||||||||||||
11 | S001 | IJ | 12/6/1993 | 5/4/1994 | 10/2/1995 | ||||||||||||||||
12 | S002 | IJ | 10/3/2001 | 1/8/2002 | |||||||||||||||||
13 | S003 | IJ | 8/22/2003 | 10/26/1995 | |||||||||||||||||
14 | S004 | ALZ | 5/5/2015 | 6/9/2015 | 12/26/2018 | ||||||||||||||||
15 | S005 | ALZ | 10/22/2015 | 11/23/2015 | 3/15/2016 | ||||||||||||||||
16 | S006 | BH | 12/16/2020 | 2/20/2023 | |||||||||||||||||
17 | S007 | BH | 1/8/2021 | ||||||||||||||||||
18 | S008 | BH | 1/8/2021 | ||||||||||||||||||
19 | S009 | BH | 1/8/2021 | ||||||||||||||||||
20 | S010 | BH | 2/8/2021 | 11/30/2022 | |||||||||||||||||
21 | S011 | IM | 4/28/2021 | 8/25/2022 | 11/22/2022 | ||||||||||||||||
22 | S012 | FJ | 5/14/2021 | 1/17/2022 | 12/8/2022 | ||||||||||||||||
23 | S013 | FJ | 5/28/2021 | 5/4/2022 | 4/24/2023 | ||||||||||||||||
24 | S014 | IM | 6/30/2021 | 8/9/2021 | 12/16/2021 | ||||||||||||||||
25 | S015 | FJ | 2/22/2022 | 11/14/2022 | 2/10/2023 | ||||||||||||||||
26 | S016 | IM | 3/23/2022 | 5/3/2022 | 2/1/2023 | ||||||||||||||||
27 | S017 | TLA | 3/24/2022 | 5/17/2022 | 4/14/2023 | ||||||||||||||||
28 | S018 | TLA | 4/26/2022 | 7/6/2022 | 8/29/2022 | ||||||||||||||||
29 | S019 | ALZ | 11/18/2022 | 2/10/2023 | 5/31/2023 | ||||||||||||||||
30 | S020 | ALZ | 11/18/2022 | 2/10/2023 | |||||||||||||||||
31 | S021 | SDF | 11/18/2022 | 2/10/2023 | |||||||||||||||||
32 | S022 | FJ | 11/18/2022 | 2/10/2023 | 5/30/2023 | ||||||||||||||||
33 | S023 | BH | 11/24/2022 | 2/8/2023 | |||||||||||||||||
34 | S024 | TLA | 12/20/2022 | 1/18/2023 | |||||||||||||||||
35 | S025 | TLA | 12/20/2022 | 3/2/2023 | |||||||||||||||||
36 | S026 | TLA | 1/20/2023 | 5/15/2023 | |||||||||||||||||
37 | S027 | ALZ | 1/20/2023 | 3/23/2023 | |||||||||||||||||
38 | S028 | ALZ | 1/27/2023 | 10/17/2022 | |||||||||||||||||
39 | S029 | TLA | 1/31/2023 | 6/6/2023 | |||||||||||||||||
40 | S030 | TLA | 2/1/2023 | 4/26/2023 | 5/1/2023 | ||||||||||||||||
41 | S031 | TLA | 2/1/2023 | 5/1/2023 | |||||||||||||||||
42 | S032 | TLA | 2/1/2023 | 3/28/2023 | 5/30/2023 | ||||||||||||||||
43 | S033 | ALZ | 2/1/2023 | ||||||||||||||||||
44 | S034 | BH | 2/13/2023 | ||||||||||||||||||
45 | S035 | FJ | 2/15/2023 | 4/3/2023 | 6/5/2023 | ||||||||||||||||
46 | S036 | FJ | 2/15/2023 | 4/3/2023 | 6/28/2023 | ||||||||||||||||
47 | S037 | FJ | 2/15/2023 | 4/3/2023 | 6/26/2023 | ||||||||||||||||
48 | S038 | FJ | 2/15/2023 | 4/3/2023 | 6/5/2023 | ||||||||||||||||
49 | S039 | FJ | 2/15/2023 | 4/3/2023 | 6/19/2023 | ||||||||||||||||
50 | S040 | FJ | 2/22/2023 | 5/9/2023 | 7/11/2023 | ||||||||||||||||
51 | S041 | FJ | 2/27/2023 | 4/13/2023 | 6/27/2023 | ||||||||||||||||
52 | S042 | FJ | 2/27/2023 | 4/13/2023 | 6/24/2023 | ||||||||||||||||
53 | S043 | BH | 6/23/2023 | ||||||||||||||||||
54 | S044 | BH | 6/23/2023 | ||||||||||||||||||
55 | S045 | BH | 6/23/2023 | ||||||||||||||||||
56 | S046 | BH | 6/23/2023 | ||||||||||||||||||
57 | S047 | BH | 7/11/2023 | ||||||||||||||||||
58 | S048 | BH | 7/11/2023 | ||||||||||||||||||
59 | S049 | BH | 7/11/2023 | ||||||||||||||||||
60 | S050 | BH | 7/11/2023 | ||||||||||||||||||
61 | |||||||||||||||||||||
62 | |||||||||||||||||||||
63 | |||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L1 | L1 | =TODAY() |
M1:O1 | M1 | =L2-1 |
P1 | P1 | =O2 |
L2 | L2 | =TODAY()-30 |
M2 | M2 | =TODAY()-90 |
N2 | N2 | =TODAY()-180 |
O2 | O2 | =TODAY()-365 |
L4:P4 | L4 | =COUNTIFS($C$11:$C$60,">="&L2,$C$11:$C$60,"<="&L1) |
K4 | K4 | =COUNTIF(C11:C4693,"<>") |
K5 | K5 | =COUNTIFS( D11:D60,"=", E11:E60,"=") |
K6 | K6 | =COUNTIFS( C11:C60,"<>", D11:D60,"=", E11:E60,"<>") |
K7 | K7 | =COUNTIFS(C10:C60,"<>", D10:D60,"<>", E10:E60, "=") |
K8 | K8 | =COUNTIF(E11:E60,"<>")-K6 |
Q4:Q8 | Q4 | =SUM(L4:P4) |
K9:P9 | K9 | =SUM(K5:K8) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
K9:P9 | Cell Value | =K4 | text | YES |
K9:P9 | Cell Value | <>K4 | text | NO |
Q4:Q8 | Cell Value | =K4 | text | YES |
Q4:Q8 | Cell Value | <>50+K4 | text | NO |
Thank you in advance for any help!