# Need some help with some formulas to count dates

#### freeb1893

##### Board Regular
I have the following data set:

 Final Submission Initial Start 6/5/2014 6/3/2014 6/19/2014 6/13/2014 6/26/2014 6/2/2014 6/26/2014 6/2/2014 7/17/2014 6/17/2014 7/14/2014 7/14/2014 7/2/2014 7/2/2014 7/24/2014 7/2/2014 7/30/2014 7/2/2014 8/5/2014 7/29/2014 8/22/2014 8/15/2014 8/22/2014 8/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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### skywriter

##### Well-known Member
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

</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))

</tbody>

<tbody>
</tbody>

#### freeb1893

##### Board Regular
Your understanding correctly, and your same results are correct. But the formulas are not working.

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>

#### skywriter

##### Well-known Member
But the formulas are not working.

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:

#### freeb1893

##### Board Regular
Nah still not working right. I'm getting a result of 0 for all the formulas.

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))``

#### freeb1893

##### Board Regular
Pretty sure the hang up is the blank cells in the columns.

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))``

#### skywriter

##### Well-known Member
Pretty sure the hang up is the blank cells in the columns.

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:

Replies
3
Views
723
Replies
1
Views
367
Replies
4
Views
270
Replies
12
Views
804
Replies
2
Views
613

### Forum statistics

1,191,197
Messages
5,985,231
Members
439,952
Latest member
djharter ### 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.

### Which adblocker are you using?    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

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