|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count # of times "text" is in 1 column is specific date is in the other column
Good Morning,
We have a workbook where we track information about incoming new hires that go through my department's orientation process. I am trying to make it so that the excel sheet automatically tabulates the following things: RNs from NB, RNs from SOM, Total RNs, CCTs from NB, CCTs from SOM, Total CCTs, PMs from NB, PMs from SOM, Total PMs, UCs from NB, UCs from SOM, and Total UCs each from their respective worksheets all in one place on a separate worksheet. I currently have 6 worksheets titled as follows: RN, CCT, PM, UC, Tabulation, & Misc Information. There are varying columns for each different worksheets but the first 10 are all the same: Last Name (A), First Name (B), Emp. #(C), Campus (D), CC# (E), Unit (F), Status (G), Transfer (H), Orientation Hours (I), & Orientation Start Date (J). I have taken a basic Excel class and tried to write out what I need from the formula to make it work, but I am still having difficulties. I have has gotten as far as =COUNTIF(RN!D1: D6,"NB") which without identifiers would simply be =COUNTIF(-range-,"-text-") I have attached images for clarification in case I was not specific enough, but please let me know if you need more images or information. I apologize that they are so big. This is fine if I put in the ranges, but I am not the only person who uses this workbook and I worry that someone will add or remove a row and have it end up tabulating the wrong amount of people for each month. Any guidance or suggestions are greatly appreciated. Thank you in advance for your response!! |
#2
|
||||
|
||||
Hello
as images are impossible to work with, and the ones provided are not very clear, could you please post a small sample sheet ( Go Advanced - Manage attachments) containing some data and expected results ? Also some explanation would help. "RNs from NB" is not very explicit
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Quote:
Good Morning, Sorry if the images did not help. I have attached a rough version with fake data entered in it. On the one sheet I put in my current formula and, below it, what I would want the data to be. I didn't think it mattered but RN, CCT, PM, and UC are just shortened job titles and SOM and NB are the codes for our 2 campuses. So when I say RNs from NB I am trying to count how many RNs from NB that were orientated that specific month. Is there any other information I can provide? |
#4
|
|||
|
|||
Try this inTabulation sheet B2
Code:
=SUMPRODUCT((Table4[Campus]="NB")*(MONTH(Table4[Oritentation Start Date])=1)) |
#5
|
|||
|
|||
You can just highlight your data and on the "Insert" Ribbon On top as shown on your picture you can select the Pivot table. On the left pane Drag the column name Orientation Date on ROWS section of the Pivot, Then drag the different dept on the VALUES section of the pivot, both to the bottom on the pane. This will give you the sum or count of each dept based on the dates. When ever new info is entered then go to the ANALYZE ribbon on top and click "Change data Source" highlight the the relevant information and it will give you the info needed. This should Help.
|
Tags |
countif, tabulate |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N | mikey386 | Excel | 2 | 12-11-2014 01:14 PM |
Set tables in email to default to "AutoFit Column Width"? | viper92283 | Outlook | 0 | 02-26-2014 03:31 PM |
Can you turn off the "autofit column widths on update" by default? | New Daddy | Excel | 0 | 11-23-2013 09:56 PM |
Custom column in "List" view, show on Contact Card | phillyhy | Outlook | 0 | 05-16-2012 11:13 PM |
"First column" in word table page break border | mj1856 | Word Tables | 1 | 04-25-2012 03:21 AM |