Countifs formula to count rows from previous month

M

mecerrato

Board Regular
Joined
Oct 5, 2015
Messages
184
Office Version
  1. 365
Platform
  1. Windows
I am trying to count all rows that falls between any day of the previous month

I am using this formula but it is not working, can someone help me correct the formula?
=COUNTIFS(CreditBranch,I10,Credit_PullDate,">="&EOMONTH(A2,-2)+1,Credit_PullDate,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),0))
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Fluff said:
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Click to expand...
unfortunately I am on a corporate computer and cannot install add-ons or post sample date. Would it be possible to provide the correct syntax for the formula to count all rows that falls between any day of the previous month
 
Upvote 0
There is nothing obviously wrong with the formula, but as you are using named ranges & not said what the actual problem is, it's very difficult to help.

If you cannot even post sample data straight from Excel into the thread, then there is not much I can do.
 
Upvote 0
Assuming the named ranges are fine, it is also critical to know what exactly is in cells A2 and I10, as you seem to be using those as criteria.
And your first condition appears to have nothing at all to do with the date check, so that could be coming into play.

I would recommend working from the ground-up, one piece at a time.
Start off checking just for one of the two date conditions, to make sure you have that piece correct.
Then, try another formula checking just for the other date condition, and verify that piece is working correct.
Once you have both working correctly, then put them together in a COUNTIFS with both date conditions.
Once that is working correctly, add in your last non-date criteria.
 
Upvote 0
Joe4 said:
Assuming the named ranges are fine, it is also critical to know what exactly is in cells A2 and I10, as you seem to be using those as criteria.
And your first condition appears to have nothing at all to do with the date check, so that could be coming into play.

I would recommend working from the ground-up, one piece at a time.
Start off checking just for one of the two date conditions, to make sure you have that piece correct.
Then, try another formula checking just for the other date condition, and verify that piece is working correct.
Once you have both working correctly, then put them together in a COUNTIFS with both date conditions.
Once that is working correctly, add in your last non-date criteria.
Click to expand...
I am sure the data and the fields I am using are fine. I am using this formula for current month count and it works fine
=COUNTIFS(CreditBranch,I10,Credit_PullDate,">="&TODAY()-DAY(TODAY())+1,Credit_PullDate,"<"&EDATE(TODAY()-DAY(TODAY()),1))
It is using the same exact named ranges and criteria except that I need previous month and not current month

The result I am getting for previous month is 0 when I can see in the date that there is at least 3

I wa actually able to use the XLBB tool, here is the data:
CreditDates.xlsx
ABCDEFGHIJKLMNOPQRS
1BranchCreditDate
2Boca Raton11/1/2022
3Boca Raton11/1/2022
4Boca Raton10/15/2022
5Boca Raton10/16/2022
6Boca Raton10/17/2022
7Boca Raton11/5/2021
8Miami Lakes10/8/2020
9Miami Lakes2/4/2021
10Miami LakesChoose BranchBoca Raton1/22/2022
11Miami LakesCurrent Month Count211/28/2021
12Miami LakesPrevious Month Count011/1/2021
13Miami Lakes12/12/2020
14Pembroke Pines2/17/2020
15Pembroke Pines5/19/2021
16Pembroke Pines6/21/2021
17Pembroke Pines10/16/2021
18Pembroke Pines7/28/2021
19Pembroke Pines3/5/2021
20Pembroke Pines2/12/2021
21Pembroke Pines8/23/2022
22Pembroke Pines7/13/2021
23Pembroke Pines3/22/2021
24Pembroke Pines3/19/2021
25Pembroke Pines3/9/2020
26Pembroke Pines4/8/2021
27Pembroke Pines5/20/2020
28Pembroke Pines2/7/2022
29Pembroke Pines9/27/2021
30Pembroke Pines8/29/2020
31Pembroke Pines5/26/2022
32Pembroke Pines3/15/2022
33Pembroke Pines7/22/2022
34Pembroke Pines5/17/2021
35Pembroke Pines9/2/2020
36Pembroke Pines7/25/2020
37Pembroke Pines3/23/2022
38Pembroke Pines4/12/2021
39Pembroke Pines11/30/2020
40Pembroke Pines3/12/2020
41Pembroke Pines11/1/2020
42Pembroke Pines2/28/2020
43Pembroke Pines8/12/2020
44Pembroke Pines3/11/2021
45Pembroke Pines9/29/2020
46Pembroke Pines12/27/2020
47Pembroke Pines5/28/2020
48Pembroke Pines2/12/2021
49Pembroke Pines2/20/2020
50Pembroke Pines3/29/2021
51Pembroke Pines5/31/2020
52Pembroke Pines8/6/2020
53Pembroke Pines10/9/2020
54Pembroke Pines10/13/2020
55Pembroke Pines10/23/2019
56Pembroke Pines6/11/2020
57Pembroke Pines2/26/2020
58Pembroke Pines6/15/2020
59Pembroke Pines1/29/2020
60Pembroke Pines1/7/2020
61Pembroke Pines2/19/2021
62Pembroke Pines9/30/2020
63South Miami8/16/2022
64South Miami6/4/2022
65South Miami2/18/2022
66South Miami9/14/2022
67South Miami7/8/2022
68South Miami8/25/2022
69South Miami1/21/2022
70South Miami8/3/2022
71South Miami4/13/2022
72South Miami10/5/2021
73South Miami7/13/2022
74South Miami2/18/2022
75South Miami6/3/2022
76South Miami10/20/2022
77South Miami6/17/2022
78South Miami9/8/2022
79South Miami9/26/2022
80South Miami9/27/2022
81South Miami7/26/2022
82South Miami8/31/2022
83South Miami1/7/2022
84South Miami12/2/2021
85South Miami10/8/2022
86South Miami11/15/2021
87South Miami9/20/2022
88South Miami6/24/2022
89South Miami11/29/2021
90South Miami3/9/2022
91South Miami8/23/2022
92South Miami9/7/2022
93South Miami9/28/2022
94South Miami8/2/2022
95South Miami2/23/2022
96South Miami9/1/2022
97South Miami9/1/2022
98South Miami5/16/2022
99South Miami1/14/2022
100South Miami9/9/2022
101South Miami1/14/2022
102South Miami8/10/2022
103South Miami1/3/2022
104South Miami12/22/2021
105South Miami8/31/2022
106South Miami8/19/2022
107South Miami2/11/2022
108South Miami8/30/2022
109South Miami6/2/2022
110Pembroke Pines2/2/2022
111Boca Raton8/1/2022
Credit Pulls
Cell Formulas
RangeFormula
I11I11=COUNTIFS(CreditBranch,I10,Credit_PullDate,">="&TODAY()-DAY(TODAY())+1,Credit_PullDate,"<"&EDATE(TODAY()-DAY(TODAY()),1))
I12I12=COUNTIFS(CreditBranch,I10,Credit_PullDate,">="&EOMONTH(A2,-2)+1,Credit_PullDate,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),0))
Named Ranges
NameRefers ToCells
Credit_PullDate='Credit Pulls'!$S$2:$S$148849I11:I12
CreditBranch='Credit Pulls'!$D$2:$D$148849I11:I12
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S1:S111,D1:D13580Expression=ROW(D1)=HighlightRowCredittextNO
Cells with Data Validation
CellAllowCriteria
I10ListBoca Raton,Pembroke Pines, Plantation, South Miami
 
Upvote 0
You don't have anything in A2, hence the formula isn't working.

Maybe it should be
Excel Formula: 
=COUNTIFS(CreditBranch,I10,Credit_PullDate,">="&EOMONTH(TODAY(),-2)+1,Credit_PullDate,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),0))
 
Upvote 0
Solution
That sample data doesn't appear to help much, as it doesn't show the named range with all the dates.
 
Upvote 0
Fluff said:
You don't have anything in A2, hence the formula isn't working.

Maybe it should be
Excel Formula: 
=COUNTIFS(CreditBranch,I10,Credit_PullDate,">="&EOMONTH(TODAY(),-2)+1,Credit_PullDate,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),0))
Click to expand...
Rookie mistake, that fixed it, thanks o_O
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Formula for Overdue based on > and < date
Replies
8
Views
210
Fluff
Fluff
P
  • Solved
Date formula
Replies
3
Views
285
pujo
P
S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
282
Tetra201
Tetra201
J
  • Question Question
Converting YEARS and MONTHS to decimal
Replies
2
Views
197
janema
J
A
  • Solved
Conditional formatting based on date in one row, text in another
Replies
3
Views
119
Joe4
Joe4

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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