Grouping dates by month and year

[Iain]

I have a spreadsheet with a range of dates in one column, and a list
of statuses in the next (basically it is a list of orders, which are
either open or closed).

I want to create a list of open orders broken down by month.

i.e. Some kind of array formula which goes down A1:A200 looks for any
from say March 2010, then looks to B1:B200 for those marked as ‘Open’,
and returns a count.

It’s been an age since I’ve taxed my brain with this kind of stuff
(been out of work) and I’m sure this is probably quite easy. However,
I’ve falling at the first hurdle, trying to find a function to match
month and year (MONTH only identifying the month number 1-12)

All insight gratefully received.

 

[Pete_UK]

Try this:

=SUMPRODUCT((TEXT(A$1:A$200,"mm yyyy")="03 2010")*(B$1:B$200="Open"))

You could, of course, put "03 2010" in a cell and refer to that cell
in the formula, and thus using other cells in that column for other
months you can just copy the fomula down to get a breakdown over
several months.

Hope this helps.

Pete

 

[Iain]

Try this:

=SUMPRODUCT((TEXT(A$1:A$200,"mm yyyy")="03 2010")*(B$1:B$200="Open"))

You could, of course, put "03 2010" in a cell and refer to that cell
in the formula, and thus using other cells in that column for other
months you can just copy the fomula down to get a breakdown over
several months.

Hope this helps.

Pete







- Show quoted text -

Pete,

Thanks for the reply. However, all I'm getting returned using this
formula is 'TRUE'....

Not sure why this would be, but in any case, I was thinking I'd want
to use COUNTIF, rather than SUM/SUMPRODUCT

 

[Pete_UK]

Check your formula - you may have missed one of the brackets.

Countif can only be used for one condition, but you have two.

Hope this helps.

Pete

 

[Iain]

Check your formula - you may have missed one of the brackets.

Countif can only be used for one condition, but you have two.

Hope this helps.

Pete





- Show quoted text -

Pete,

You are right! I thought I'd done a simple copy 'n' paste but
obviously not...

I still have a problem though, when I try and replace the "03 2010"
with a cell reference.

 

[Pete_UK]

Suppose you put 03 2010 in cell D2 (note the space between 03 and
2010). Then the formula becomes:

=SUMPRODUCT((TEXT(A$1:A$200,"mm yyyy")=D2)*(B$1:B$200="Open"))

You could put other month/years in the same format in D3, D4, D5 etc,
and just copy the formula down to get counts for those months.

Hope this helps.

Pete

 

[Iain]

Suppose you put 03 2010 in cell D2 (note the space between 03 and
2010). Then the formula becomes:

=SUMPRODUCT((TEXT(A$1:A$200,"mm yyyy")=D2)*(B$1:B$200="Open"))

You could put other month/years in the same format in D3, D4, D5 etc,
and just copy the formula down to get counts for those months.

Hope this helps.

Pete





- Show quoted text -

Aaaahhh! I'd had D2 populated with a date in MMM YY format, but what
you're doing is a text look-up derived from a cell with a formated
date.

Many thanks for your help.

 

[Pete_UK]

You're welcome, Iain - thanks for feeding back.

Pete