Countifs question

Sarasota

New Member
Joined
Jul 26, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:

countifs-issues.xlsx
ABCDEFGHIJKLMNOPQRS
17/26/20236/25/20234/26/20231/26/20237/26/2022
26/26/20234/27/20231/27/20237/26/20221/1/1993
3Count< 30 days31 - 90 Days91 days - 180 days181 - 365 days1 year +Check
4Inventory (all stages)50441591850
5Stage 2 (active)130
6Placed Stage 320
7Stage 2 (stage / no stage 3)120
8Stage 3 230
9Check2 5000000
10AccountDeskStage1Stage2Stage3
11S001IJ12/6/19935/4/199410/2/1995
12S002IJ10/3/20011/8/2002
13S003IJ8/22/200310/26/1995
14S004ALZ 5/5/20156/9/201512/26/2018
15S005ALZ 10/22/201511/23/20153/15/2016
16S006BH 12/16/20202/20/2023
17S007BH 1/8/2021
18S008BH 1/8/2021
19S009BH 1/8/2021
20S010BH 2/8/202111/30/2022
21S011IM 4/28/20218/25/202211/22/2022
22S012FJ 5/14/20211/17/202212/8/2022
23S013FJ 5/28/20215/4/20224/24/2023
24S014IM 6/30/20218/9/202112/16/2021
25S015FJ 2/22/202211/14/20222/10/2023
26S016IM 3/23/20225/3/20222/1/2023
27S017TLA 3/24/20225/17/20224/14/2023
28S018TLA 4/26/20227/6/20228/29/2022
29S019ALZ 11/18/20222/10/20235/31/2023
30S020ALZ 11/18/20222/10/2023
31S021SDF 11/18/20222/10/2023
32S022FJ 11/18/20222/10/20235/30/2023
33S023BH 11/24/20222/8/2023
34S024TLA 12/20/20221/18/2023
35S025TLA 12/20/20223/2/2023
36S026TLA 1/20/20235/15/2023
37S027ALZ 1/20/20233/23/2023
38S028ALZ 1/27/202310/17/2022
39S029TLA 1/31/20236/6/2023
40S030TLA 2/1/20234/26/20235/1/2023
41S031TLA 2/1/20235/1/2023
42S032TLA 2/1/20233/28/20235/30/2023
43S033ALZ2/1/2023
44S034BH 2/13/2023
45S035FJ2/15/20234/3/20236/5/2023
46S036FJ2/15/20234/3/20236/28/2023
47S037FJ2/15/20234/3/20236/26/2023
48S038FJ2/15/20234/3/20236/5/2023
49S039FJ2/15/20234/3/20236/19/2023
50S040FJ2/22/20235/9/20237/11/2023
51S041FJ2/27/20234/13/20236/27/2023
52S042FJ2/27/20234/13/20236/24/2023
53S043BH6/23/2023
54S044BH6/23/2023
55S045BH6/23/2023
56S046BH6/23/2023
57S047BH7/11/2023
58S048BH7/11/2023
59S049BH7/11/2023
60S050BH7/11/2023
61
62
63
Sheet1
Cell Formulas
RangeFormula
L1L1=TODAY()
M1:O1M1=L2-1
P1P1=O2
L2L2=TODAY()-30
M2M2=TODAY()-90
N2N2=TODAY()-180
O2O2=TODAY()-365
L4:P4L4=COUNTIFS($C$11:$C$60,">="&L2,$C$11:$C$60,"<="&L1)
K4K4=COUNTIF(C11:C4693,"<>")
K5K5=COUNTIFS( D11:D60,"=", E11:E60,"=")
K6K6=COUNTIFS( C11:C60,"<>", D11:D60,"=", E11:E60,"<>")
K7K7=COUNTIFS(C10:C60,"<>", D10:D60,"<>", E10:E60, "=")
K8K8=COUNTIF(E11:E60,"<>")-K6
Q4:Q8Q4=SUM(L4:P4)
K9:P9K9=SUM(K5:K8)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K9:P9Cell Value=K4textYES
K9:P9Cell Value<>K4textNO
Q4:Q8Cell Value=K4textYES
Q4:Q8Cell Value<>50+K4textNO


Thank you in advance for any help!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Thanks to all that looked I figured it out. Just continue the countif statement.

=COUNTIFS(G11:G4693,"<>",H11:H4693,"<>", I11:I4693,"=")
Isolated the specific accounts

=COUNTIFS($G$11:$G$4693,"<>",$H$11:$H$4693,"<>", $I$11:$I$4693,"=",$H$11:$H$4693,">="&U1,$H$11:$H$4693,"<="&U2)
segmented the isolated accounts by dates (U1 = before date and U2 = after date on a cheat table)

Yes, I know there is probably an easier way to get this but this was short and sweet and got my report completed.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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