COUNTIF on partial cell data

[FinchC]

Hi Guys,

What i am trying to be able to do is to perform a countif in a certain range, however i only need to countif part of the cell.

For example the data in my cells read '11 Nov 2010 00:00:00' but i am only interesting in counting 'Nov 2010'. Then ths will hopefully show me how many 'Nov 2010' 'Dec 2010' 'Jan 11' etc are within that range.

Can anyone offer me assistance.

Cheers.

Chris.

[NBVC]

Are those dates formatted to look like that or are they text entries (i.e. text formatted cell or apostrophe in front"?

[FinchC]

The date range is extracted straight from Microsoft CRM, and they need to remain like this for calculating timescales. They have not been changed.

[NBVC]

So assuming your data is in A1:A10

and your lookup date is in D2, try:

=SUMPRODUCT(--(TEXT($A$1:$A$11,"mmm-yy")=TEXT(D2,"mmm-yy")))

or, if enter the value in D2 as text Jan-01

=SUMPRODUCT(--(TEXT($A$1:$A$11,"mmm-yy")=D2)

You can adjust range to suit or change the "mmm-yy" format to suit what you have in D2.

[FinchC]

For this instance and most the range I would be looking in would be the whole of column Q as all the dates are in this range. So with this in mind would my formula be;

=SUMPRODUCT(--(TEXT($Q$1:$Q$11,"Jan-2010")=Q2)

Im very new to this.

[NBVC]

You have to have a separate cell somewhere where you would input the Month-Year you want to look for... e.g. Jan 1, 2010 and you can format this as Date (mm-yyyy) or whatever you like, in the next cell you would enter Feb 1, 2010 and so on, then let's assume you entered that starting in a new empty column like in say R2.

then apply the formula:

=SUMPRODUCT(--(TEXT($Q$1:$Q$11,"mm-yy")=TEXT(R2,"mm-yy")

you can expand (Q1:Q11 to encompass more of the column, but it is not recommended to use the whole column as it will slow down the process).

[NBVC]

I added a sample workbook above

[FinchC]

I have also attached a file above to show you exactly what i mean, the data in column C for example is how the report extracts it, and i do not want to change this. However what i want is to only count the month and the year exactly how it is written i.e 'Jan 2010'. Then put the totals in G3,G4 and G5 and so on. Could you modify mine to show how to do it and explain.

[NBVC]

Put this formula in G3 and copy down.

=SUMPRODUCT(--(TEXT($C$2:$C$26+0,"mmm-yy")=TEXT(F3,"mmm-yy")))