Need some help with some formulas to count dates

[freeb1893]

I have the following data set:

[TABLE="width: 94"]
<colgroup><col style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" width="61"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl78, width: 61, bgcolor: #974706"]Final Submission[/TD]
[TD="class: xl79, width: 64, bgcolor: transparent"]Initial Start[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]6/5/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]6/3/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]6/19/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]6/13/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]6/26/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]6/2/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]6/26/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]6/2/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]7/17/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]6/17/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]7/14/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/14/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]7/2/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/2/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]7/24/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/2/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]7/30/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/2/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]8/5/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/29/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]8/22/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]8/15/2014[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]8/22/2014[/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]8/13/2014[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]9/2/2014[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/9/2014[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]6/26/2014[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/23/2014[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/9/2014[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/9/2014[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent, align: right"]7/29/2014[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]



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.

 

[skywriter]

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
Initial Start
Criteria 1	Criteria 2	Criter ia 3	Criteria 4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #974706"]Final Submission[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6/5/2014[/TD]
[TD="align: right"]6/3/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6/19/2014[/TD]
[TD="align: right"]6/13/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6/26/2014[/TD]
[TD="align: right"]6/2/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6/26/2014[/TD]
[TD="align: right"]6/2/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]7/17/2014[/TD]
[TD="align: right"]6/17/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7/14/2014[/TD]
[TD="align: right"]7/14/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7/2/2014[/TD]
[TD="align: right"]7/2/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7/24/2014[/TD]
[TD="align: right"]7/2/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]7/30/2014[/TD]
[TD="align: right"]7/2/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8/5/2014[/TD]
[TD="align: right"]7/29/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]8/22/2014[/TD]
[TD="align: right"]8/15/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]8/22/2014[/TD]
[TD="align: right"]8/13/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9/2/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/9/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/26/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/23/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/9/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/9/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/29/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[freeb1893]

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
Initial Start
Criteria 1	Criteria 2	Criter ia 3	Criteria 4

<TBODY>
[TD="align: center"]1
[/TD]
[TD="bgcolor: #974706"]Final Submission
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]6/5/2014
[/TD]
[TD="align: right"]6/3/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]6/19/2014
[/TD]
[TD="align: right"]6/13/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]6/26/2014
[/TD]
[TD="align: right"]6/2/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]6/26/2014
[/TD]
[TD="align: right"]6/2/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]7/17/2014
[/TD]
[TD="align: right"]6/17/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]7/14/2014
[/TD]
[TD="align: right"]7/14/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]7/2/2014
[/TD]
[TD="align: right"]7/2/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]7/24/2014
[/TD]
[TD="align: right"]7/2/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"]7/30/2014
[/TD]
[TD="align: right"]7/2/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"]8/5/2014
[/TD]
[TD="align: right"]7/29/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"]8/22/2014
[/TD]
[TD="align: right"]8/15/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"]8/22/2014
[/TD]
[TD="align: right"]8/13/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9/2/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/9/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6/26/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/23/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/9/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/9/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7/29/2014
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="bgcolor: #dae7f5"]E3
[/TH]
[TD="align: left"]=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-2))
[/TD]
[/TR]
[TR]
[TH="bgcolor: #dae7f5"]F3
[/TH]
[TD="align: left"]=SUMPRODUCT((--(MONTH(A2:A21)=MONTH(TODAY())-1)),--(MONTH(B2:B21)=MONTH(TODAY())-1))
[/TD]
[/TR]
[TR]
[TH="bgcolor: #dae7f5"]G3
[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-2))
[/TD]
[/TR]
[TR]
[TH="bgcolor: #dae7f5"]H3
[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2:A21=0),--(MONTH(B2:B21)=MONTH(TODAY())-1))
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]

 

[skywriter]

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.

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

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

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

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

 

[freeb1893]

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.

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

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

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

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

 

[freeb1893]

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.

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

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

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

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

 

[skywriter]

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.