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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|