Counting Text Occurances - HELP PLEASE

I have numerous (approx. 30) 2 letter codes used to identify different type 
of work events.  Some are "soft" events, some are "hard" events.  I want to 
count how many times each type of event occurs in a range.

Soft = DO, VC, SK, JD, CP, etc.
Hard = FT, TH, WK, MT, etc.

My data is entered across columns by date and down rows by employee.  Does 
anyone know how to sum the "soft" and "hard" events by row / employee?  
Thanks. 
0
Utf
1/12/2010 10:50:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1160 Views

Similar Articles

[PageSpeed] 7

Hi,

Assume that your first employee data is in row 21 (Let's say the workevents 
are in D21:I21).  In C26:C31, enter the soft codes and in D26:D29, enter the 
hard codes.  In cell J21, enter the following formula to count the soft 
codes

=SUMPRODUCT(1*(ISNUMBER(MATCH($D21:$I21,$C$26:$C$30,0))))

For hard codes

=SUMPRODUCT(1*(ISNUMBER(MATCH($D$21:$I$21,$D$26:$D$30,0))))

Hope this helps

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Beauty Girl" <Beauty Girl@discussions.microsoft.com> wrote in message 
news:283D5590-CF4B-4C85-B5C1-E2FCBFB43D62@microsoft.com...
> I have numerous (approx. 30) 2 letter codes used to identify different 
> type
> of work events.  Some are "soft" events, some are "hard" events.  I want 
> to
> count how many times each type of event occurs in a range.
>
> Soft = DO, VC, SK, JD, CP, etc.
> Hard = FT, TH, WK, MT, etc.
>
> My data is entered across columns by date and down rows by employee.  Does
> anyone know how to sum the "soft" and "hard" events by row / employee?
> Thanks. 

0
Ashish
1/13/2010 12:13:09 AM
Reply:

Similar Artilces:

Help with formula #18
Hi All: I am WAY over my head here, and you all have always been great help, so hopefully someone can help with this. I had someone write this for me to use as an index at the top of a spread sheet. It works beautifully . The problem is I would like to move this index to sheet 2 of the same spreadsheet and when I copy and paste it, it does'nt work. Is there anyone who can modify this to work as an index in sheet 2 and will search sheet 1 for the results? TIA Keith =IF(#REF!="","",IF(ISNA(MATCH(#REF!,$H$31:$H$65536,0)),"",HYPERLINK("#"&C...

Help needed with Money 2003 files
Hi there, I've been using money 2003 for years even when upgrading to new PC's as I had the original disk. I've just had to rebuild my hard drive and now cannot find the disk anywhere so cannot install Money. As you cannot now buy Money, I've tried downloading the trial version of Money so that I can access all the account details/balances but it says that they're not compatible with earlier versions of money. Is there anything that I can do to get these files open or have I lost all my records for the last 6 years? I'd really appreciate your assistance. Jayne EggHead...

How do you combine two columns to one? Help!!!!!!
I have address numbers in column A and street names in column B, I want to combine all of both columns into one without going through the whole process for each individual cells. HELP!!!!!! "sttrumpet" <sttrumpet@discussions.microsoft.com> wrote in message news:F72E43CE-CA99-4CD0-BCA1-85EFADCB459D@microsoft.com... > I have address numbers in column A and street names in column B, I want to > combine all of both columns into one without going through the whole process > for each individual cells. HELP!!!!!! Use the fuction =A1&B1 in cell B1 Drag the function ...

VB Code help
Here is a portion of one of my VB Macros in Excel: If Selection.Count > 0 Then MsgBox ("Average = ") & (RunningTotal / Selection.Count), vbInformation, "Average" End If My question: How do I format the number (RunningTotal/Selection.Count) to look like 0.000, instead of 0.0000000000000000?? What should my code look like with the new formatting. Thanks Chris Chris, Try this: ....Format((RunningTotal / Selection.Count), "0.000")... -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- &qu...

Cell text color change
I'm trying to get the text color in a cell to change (to red) if the formula in that cell produces a value above a certain ammount... Could this be done? Look at Format>Conditional Formatting -- HTH RP (remove nothere from the email address if mailing direct) "Steffen" <Steffen@discussions.microsoft.com> wrote in message news:BC53FC5B-81DD-4872-BDCF-A56B42C24159@microsoft.com... > I'm trying to get the text color in a cell to change (to red) if the formula > in that cell produces a value above a certain ammount... > > Could this be done? ...

80040e19 error occurring with 2nd NIC installed
I am experiencing the 80040e19 error when expanding public folders in Exchange System Manager, Exchange 2000. I have exhausted all suggested MS articles and *still* have not resolved the issue. No evidence of URLScan or iislockdown being run on the server. Strange thing is is that when I disable the newly installed NIC through device manager the public folders can be expanded perfectly. On enabling the NIC, same error. The server is the PDC, Exchange and Intranet server, running IIS on port 80 for local intranet site and the admin website on port 7715. I have checked the host head...

Need help with using a bar graph in a report
I have a report that separates information about representatives by manager. I need the graphs to give a quick summary on a few stats from each rep but only show for that manager's team and not all information. For example: Manager A has RepA, RepB, and RepC under him. Each rep has Aux1, Aux2, Aux3 information which needs to be displayed. My goal is to show Manager A with his Reps A,B, and C of their Aux1, 2, and 3. Then, under the next manager Header I want Manager B to show Rep D, E, and F with their Aux1, 2, and 3 information. Etc thru Manager G. Please help... Th...

Help with Userform
I need to setup a restricted means of allowing users to edit data in existing cells on a spreadsheet through a macro (userform?). I want to create a userform that will read data from these existing cells and display their contents in a series of text boxes. The user will then have the choice to edit any data item, if they need to, or leave the data as is. Upon the user hitting the OK key on the userform, the edited data will be entered in their previous cell locations. Any help or examples will be appreciated! Thanks ...

why do pictures and text change size on printing?
making birthday card , which i have done many times, but when trying to print, the pictures and text are reducing in size from 168% to 99% . what am i now doing wrong ? ...

populating from sheets -- and PLEASE don't tell me I need VB!
Stupid newbie question, Excel 2003: I have a 2 sheet document. SOME of the info from Sheet 1 needs to appear on Sheet 2. For example, Sheet 1 Column G values need to be populated to Sheet 2 as Column B, Column H needs to be Column D, etc. Is there any way to automate this without having to write a VB script? I know a macro could kind of do it, but I don't think it could take all the parameters, since Sheet 1 is constanly being altered (i.e., new values, etc.). Oh, and all rows are relational so that G3 value from Sheet 1 when it goes to Sheet 2 B17 also has to have H3 from Shee...

Error Occured Trying to Promote this email
This happens when trying to promote a email to CRM activity. Can anyone help? Bob ...

Combining IF and Count functions
I am a novice with excel and I need assistance creating a formula that will count the number of people that appear by date. How can I ask excel to complete the following calculation: If the date is "10/11/05" count each occurrence of "John"? Here is my data sample: Completion Date Contact Type Person 10-11-2005 Letter John 10-12-2005 Mail Paul 10-11-2005 Mail Richard 10-11-2005 Letter Paul 10-12-2005 Letter Paul 10-12-2005 Letter Paul 10-12-2005 Letter Paul 10-12-2005 Mail Richard 10-12-2005 Letter Paul 10-11-2005 Mail Richard 10-12-2005 Mail Jim 10-12-2005 Letter Pa...

amend a PO with a query? Please help!
Hello, Our new warehouse manager entered, received and committed a PO from the wrong vendor - over 100 items! Does anyone know a way to either reverse the PO or change the vendor? Please help!!!! Thank you! diana Diana, You can reverse the PO by opening it again in receiving mode. Use the Quick Scan button and add an item to the PO (any item). This will 'unlock' or re-open the PO. You can now delete the item you just added back off. Next you would enter the quantity 'Received to Date' of each item into the 'Quantity Received' column as a negative. Fo...

Please Help...I can not send/receive or delete email on outlook 2000
It gives an the following errors" Error has been detected in the file C:|Windoews\Outlook.pst. quit all mail applications and then use Inbox Repair tool." I spent hours tring to find a solution on the support section. Couldn't find it.. Thank you so much for help.. What size is yr *.pst? OL prior to 2003 can have problems when greater than 1.7gb The inbox repair tool is scanpst.exe If oversize; http://support.microsoft.com/?kbid=296088 David "Daniel" <dqiab@yahoo.com> wrote in message news:05a401c3676d$0ef74c50$a401280a@phx.gbl... > It gives an the follow...

Remove Duplicate
I used OCR software to read the Names and addresses off ~500k images. For each image we received ID and Account Number combinations that correspond to a Name and Address. Within the data set there are several examples of each ID, Account, Name and Address combination. We are interested in removing the duplicates and only having the distinct records. The software is not 100% accurate so the Names and Addresses can be different from result to result for the same ID/Account. My thought was to use a majority voting approach. Below is a link to an example of the results. ...

Need help with setting IFrame script
I got the perhaps overly ambitious idea of customizing the Account screen to load the map and directions from our office to the customer site. I am using the DirectionsFind url, though I may switch to MapQuest if I can find the parameter list. Which I use, however, is a moot point if I can't figure out how to make it work in an IFrame. I have written and debugged the JavaScript that concatenates the http command for generating the map and directions from one point to the next. I know the command works because I've pasted the results into my browser and it brings up the corre...

oblique text
Is there a way to make a text oblique, using CHARFORMAT? I don't think there is... you could use CFE_ITALIC to make it italics, but if you want to do anything fancier you'll likely have to do it graphically or do your own font. That's just what I know though. Someone else may have a better idea. I did find this article that may be of interest to you: http://69.10.233.10/KB/cpp/oblique_txt.aspx Tom "Yoavo" <yoav@cimatron.co.il> wrote in message news:OEDgEAIoIHA.4928@TK2MSFTNGP04.phx.gbl... > Is there a way to make a text oblique, using CHARFORMAT? >...

help, derag won't work and the outlook folder shows 36000 fragmnets
defragmented and the program works fine but when it finishes it looks the same on the as the original analysis. Then I look at the report and it list a lot of files that can't be drfragmented. The only thing I can think of is that this pc was once on MSexchange (the reason I mention it is that the outlook pst which is 1.8 gigs, shows 36000 fragments and it is located in the c/exchange folder) Anyway, what do I do??? how can I get the drive to defrag? Hello Bob When you open IE\Properties\Programs what do you see in there under E-Mail box. Use the dropdown arrow and take a peek on ...

Converting TOC to Text
I cut and paste text from a table of contents into a Word 2003 document. It is very lengthy and I'd rather not re-type the whole long list, since it would take a very long time. However, the TOC formatting is still in the document and I can't get rid of it. I can't include hyperlinks or change font color as a result. I've tried clearing all formatting without effect. Anyone know how to convert a TOC into text? Thanks! Ctrl+Shift+F9 will unlink it. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org &q...

text and number separation
Hello everyone... I'm trying to do a spreadsheet that logs contact names and number pulled from a network application. Normally I copy and paste the information into one cell. (cell a1) Ex: Steven Tyler p: 123-456-7890 f:369-125-1821. How can I get 3 cells to seperate these and return: Steven Tyler (in b1) 123-456-7890 (in c1) 369-125-1821 (in d1) i cannot get it to remove the "p:" or "f:". Please help if possible> Thanks, SJ -- SubliminalJones ------------------------------------------------------------------------ SubliminalJones's Profile: http:/...

Please help
Only one of my users (who us utilizing IE8) is getting the following error when creating a new Timesheet in PWA 'g_oMainForm_name' is null or not an object mytssummary.aspx Code: 0 URI:http://[project server]/mytssummary.aspx Many other folks are using Timesheets - some with IE8 - and it works fine. This is the only user so far who has reported this. Any help would be greatly appreciated. Thanks! Andy Novak UNT Hi Andy, Maybe this will help: http://allfaq.org/forums/t/136987.aspx "anovak@unt.edu" wrote: > Only one of my users (who us...

Help in macro in excel.
Hi all. I want to write a macro in an excel sheet wherein I want to copy a few cells from my source worksheet and paste their hyperlink to another worksheet. Also, the destination worksheet's cell number where this is pasted is not fixed. How do I accomplish this? Please help urgently. Thanks. SPG, Running the macro recorder whilst inserting a hyperlink yields this code: ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "Sheet1!A1", TextToDisplay:="""go to hell""" Now just substitute the relevant stuff into e...

HELP PLEASE!!! #2
This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C5D651.83566720 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0019_01C5D651.83566720" ------=_NextPart_001_0019_01C5D651.83566720 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable how do i get this background off this?? im very new... thanks.... =20 ------=_NextPart_001_0019_01C5D651.83566720 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-/...

Help creating a list
Hi, I have a list of values in a single column. Many of the values are repeated. I need to create a list from this, where each value only appears one time, and capture in a new tab. Example Tab 1: 2008 Record A 2008 Record A 2009 Record B 2010 Record B 2010 Record B Result Needed in Tab 2: 2008 Record A 2009 Record B 2010 Record B Appreciate any help....Thanks ...

my excel does not work properly, please help !!
hi there, i got all the Microsoft office programs on my laptop. Excel use to work perfectly, till last week or so. when i open it or open an excel program; it opens as if its " INTALLING " excel. it gathers information to install it then it says File could not be found on C:\Program Files\ then it asks me to BROWSE to look for the file. please help me, i need excel to work very soon. aa p.s. i had the office CD back home .. im in another country for couple of months. and you know how much it costs to buy a new 1. help me .... ...