Prev: Counting cells with conditional formatting
Next: Alternate Rounding Up & Down of .50 in a column
From: Edmund on 2 Jun 2010 10:36
I have a user who is using Excel as a database – ugh. One of their columns
(fields) has contact data points. As an example, a cell might state –
“05/10/2009 – Called person. 05/24/2009 – Sent letter. 06/30/2009 – Received
call from person. 07/31/2009 – Closed case. 01/10/2010 – Reopened case.
01/24/2010 – Sent update letter.” The user wants to count all contacts within
the spreadsheet for the month of May 2010.

The first thing I am doing is having the dates changed from mm/dd/yyyy to dd
MMM yyyy format so we can search for MMM yyyy. After this is done, we can do
a Find all and count the number of rows, which have MMM yyyy in the cell;
however, if there are two occurrences of MMM yyyy in a cell the find all only
shows it once. Is there a function I can use to count all occurrences within
the spreadsheet regardless?

Thanks!

From: Steve Dunn on 2 Jun 2010 17:23
Hi Edmund, try this:

=SUMPRODUCT(LEN($B$2:$B$50)-
LEN(SUBSTITUTE($B$2:$B$50,"May 2010","")))/8



"Edmund" <Edmund(a)discussions.microsoft.com> wrote in message
news:E990C69A-D7F5-41E7-B5F4-9C7E750B77EA(a)microsoft.com...
>I have a user who is using Excel as a database – ugh. One of their columns
> (fields) has contact data points. As an example, a cell might state –
> “05/10/2009 – Called person. 05/24/2009 – Sent letter. 06/30/2009 –
> Received
> call from person. 07/31/2009 – Closed case. 01/10/2010 – Reopened case.
> 01/24/2010 – Sent update letter.” The user wants to count all contacts
> within
> the spreadsheet for the month of May 2010.
>
> The first thing I am doing is having the dates changed from mm/dd/yyyy to
> dd
> MMM yyyy format so we can search for MMM yyyy. After this is done, we can
> do
> a Find all and count the number of rows, which have MMM yyyy in the cell;
> however, if there are two occurrences of MMM yyyy in a cell the find all
> only
> shows it once. Is there a function I can use to count all occurrences
> within
> the spreadsheet regardless?
>
> Thanks!
>

 | 
Pages: 1
Prev: Counting cells with conditional formatting
Next: Alternate Rounding Up & Down of .50 in a column