A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Counting Occurrence of a Value within Cells



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 03:36 PM posted to microsoft.public.excel.worksheet.functions
Edmund
external usenet poster
 
Posts: 15
Default Counting Occurrence of a Value within Cells

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!

  #2  
Old June 2nd, 2010, 10:23 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Counting Occurrence of a Value within Cells

Hi Edmund, try this:

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



"Edmund" wrote in message
...
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!


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.