Need some help with some formulas to count dates

F

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
234
Office Version
  1. 365
Platform
  1. Windows
I have the following data set:

Final SubmissionInitial Start
6/5/20146/3/2014
6/19/20146/13/2014
6/26/20146/2/2014
6/26/20146/2/2014
7/17/20146/17/2014
7/14/20147/14/2014
7/2/20147/2/2014
7/24/20147/2/2014
7/30/20147/2/2014
8/5/20147/29/2014
8/22/20148/15/2014
8/22/20148/13/2014
9/2/2014
7/9/2014
6/26/2014
7/23/2014
7/9/2014
7/9/2014
7/29/2014

<colgroup><col style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" width="61"> <col style="width: 48pt;" width="64"> <tbody>
</tbody>



I need 4 formulas to crunch the following stats:
1.) Count the number of cases where the dates in the 2nd column are 2 month's behind the month of today, along with adjacent dates in the 1st column that are 1 month behind the month of today.
2.) Count the number of cases where the dates in the 2nd column are 1 month behind the month of today, along with adjacent dates in the 1st column that are 1 month behind the month of today.
3.) Count the number of cases where the dates in the 2nd column are 2 month's behind the month of today, along with adjacent dates in the 1st column that are blank.
4.) Count the number of cases where the dates in the 2nd column are 1 month behind the month of today, along with adjacent dates in the 1st column that are blank.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
I think I understand your request. If not give me some examples of what the results should be.

Excel 2010
ABCDEFGH
1Final SubmissionInitial Start
26/5/20146/3/2014Criteria 1Criteria 2Criter ia 3Criteria 4
36/19/20146/13/20141250
46/26/20146/2/2014
56/26/20146/2/2014
67/17/20146/17/2014
77/14/20147/14/2014
87/2/20147/2/2014
97/24/20147/2/2014
107/30/20147/2/2014
118/5/20147/29/2014
128/22/20148/15/2014
138/22/20148/13/2014
149/2/2014
157/9/2014
166/26/2014
17
187/23/2014
197/9/2014
207/9/2014
217/29/2014

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
E3=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-2))
F3=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-1))
G3=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-2))
H3=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Your understanding correctly, and your same results are correct. But the formulas are not working.

skywriter said:
I think I understand your request. If not give me some examples of what the results should be.

Excel 2010
A
B
C
D
E
F
G
H
1
Final Submission
Initial Start
2
6/5/2014
6/3/2014
Criteria 1
Criteria 2
Criter ia 3
Criteria 4
3
6/19/2014
6/13/2014
1
2
5
4
6/26/2014
6/2/2014
5
6/26/2014
6/2/2014
6
7/17/2014
6/17/2014
7
7/14/2014
7/14/2014
8
7/2/2014
7/2/2014
9
7/24/2014
7/2/2014
10
7/30/2014
7/2/2014
11
8/5/2014
7/29/2014
12
8/22/2014
8/15/2014
13
8/22/2014
8/13/2014
14
9/2/2014
15
7/9/2014
16
6/26/2014
17
18
7/23/2014
19
7/9/2014
20
7/9/2014
21
7/29/2014

<TBODY>
</TBODY>
Sheet1​

Worksheet Formulas
Cell
Formula
E3
=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-2))
F3
=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-1))
G3
=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-2))
H3
=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-1))

<TBODY>
</TBODY>


<TBODY>
</TBODY>
Click to expand...
 
Upvote 0
But the formulas are not working.
Click to expand...

The results you see are from the formulas, not hand typed. If you are typing the formulas in manually there may be a syntax error. I would suggest copying and pasting the formulas and then just changing the cell references. If you want to copy the formulas be careful to set the relative and absolute references to match your needs.

Maybe these will copy and paste better than the html maker examples.

Code: 
=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-2))

Code: 
=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-1))

Code: 
=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-2))

Code: 
=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-1))
 
Last edited:
Upvote 0
Nah still not working right. I'm getting a result of 0 for all the formulas.

skywriter said:
The results you see are from the formulas, not hand typed. If you are typing the formulas in manually there may be a syntax error. I would suggest copying and pasting the formulas and then just changing the cell references. If you want to copy the formulas be careful to set the relative and absolute references to match your needs.

Maybe these will copy and paste better than the html maker examples.

Code: 
=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-2))

Code: 
=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-1))

Code: 
=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-2))

Code: 
=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-1))
Click to expand...
 
Upvote 0
Pretty sure the hang up is the blank cells in the columns.

skywriter said:
The results you see are from the formulas, not hand typed. If you are typing the formulas in manually there may be a syntax error. I would suggest copying and pasting the formulas and then just changing the cell references. If you want to copy the formulas be careful to set the relative and absolute references to match your needs.

Maybe these will copy and paste better than the html maker examples.

Code: 
=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-2))

Code: 
=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-1))

Code: 
=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-2))

Code: 
=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-1))
Click to expand...
 
Upvote 0
freeb1893 said:
Pretty sure the hang up is the blank cells in the columns.
Click to expand...

The data used was copied from your original post and pasted into a spreadsheet. It has all the blank cells you see. You need to see if there's a difference in your actual data for these blank cells, maybe check the formatting for those cells or if they have spaces in them etc. You are going to just use a systematic troubleshooting approach. Put dates in all the blank cells and make sure the formula works without blanks. Use formula evaluator and see what the results are for each step of the formula. You can also tray using "" instead of 0 in the formula and see if that makes a difference.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Conditional top 10 Modes
Replies
1
Views
188
smit3446
S
A
  • Question
Make a table from data which updated monthly.
Replies
1
Views
329
adnan1975
A
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
72
Tetra201
Tetra201
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
347
harzer
H
M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
521
mrshuck
M

Forum statistics

Threads
1,221,408
Messages
6,159,702
Members
451,586
Latest member
khaledshahin

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