I have an Excel spreadsheet which includes a column containing dates.

I am trying to create a formula which will tell me the number of cells in
the column that have dates from last month. For some unknown reason I am
having great difficulty with this - even before trying to ensure the formula
will cope with the December/January issue, when the 'month-1' principle
presumably will not work.

Can anyone help please?

Thanks,

V

assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))


"Victor Delta" wrote:

I have an Excel spreadsheet which includes a column containing dates.

I am trying to create a formula which will tell me the number of cells in
the column that have dates from last month. For some unknown reason I am
having great difficulty with this - even before trying to ensure the formula
will cope with the December/January issue, when the 'month-1' principle
presumably will not work.

Can anyone help please?

Thanks,

V

"JMB" wrote in message
...
assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))

JMB

Many thanks. Works brilliantly!

However, the only thing I don't understand is the two hyphens at the start
of the first bracket?

Regards,

V

"Victor Delta" wrote in message ...
"JMB" wrote in message
...
assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))

JMB

Many thanks. Works brilliantly!

However, the only thing I don't understand is the two hyphens at the start
of the first bracket?

Two minus at the front convert text to numeric, such as making a number text value negative
then positive.

"David McRitchie" wrote in message
...
"Victor Delta" wrote in message
...
"JMB" wrote in message
...
assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))

JMB

Many thanks. Works brilliantly!

However, the only thing I don't understand is the two hyphens at the
start of the first bracket?

Two minus at the front convert text to numeric, such as making a number
text value negative
then positive.

Many thanks,

V

David is correct, but the double minus also converts true/false values to 1
and 0 respectively. This allows you to sum the true/false values and get a
count of how many trues there were.

Bob Phillips has an excellent discussion on sumproduct here
http://xldynamic.com/source/xld.SUMPRODUCT.html

"Victor Delta" wrote:

"JMB" wrote in message
...
assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))

JMB

Many thanks. Works brilliantly!

However, the only thing I don't understand is the two hyphens at the start
of the first bracket?

Regards,

V

"JMB" wrote in message
...
David is correct, but the double minus also converts true/false values to
1
and 0 respectively. This allows you to sum the true/false values and get
a
count of how many trues there were.

Bob Phillips has an excellent discussion on sumproduct here
http://xldynamic.com/source/xld.SUMPRODUCT.html

Thanks for that.

To be honest, I had not come across the Excel sumproduct function before. It
looks very powerful so I will enjoy reading this interesting article.

V