Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-30-2016, 08:06 AM
ann.acornacchio ann.acornacchio is offline Count # of times "text" is in 1 column is specific date is in the other column Windows 10 Count # of times "text" is in 1 column is specific date is in the other column Office 2010 64bit
Novice
Count # of times "text" is in 1 column is specific date is in the other column
 
Join Date: Dec 2016
Posts: 2
ann.acornacchio is on a distinguished road
Default 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!!
Attached Images
File Type: jpg Excel Question Image 1.jpg (156.5 KB, 14 views)
File Type: jpg Excel Question Image 2.jpg (230.0 KB, 14 views)
Reply With Quote
  #2  
Old 12-30-2016, 09:43 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Count # of times "text" is in 1 column is specific date is in the other column Windows 7 64bit Count # of times "text" is in 1 column is specific date is in the other column Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,436
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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
Reply With Quote
  #3  
Old 12-30-2016, 10:46 AM
ann.acornacchio ann.acornacchio is offline Count # of times "text" is in 1 column is specific date is in the other column Windows 10 Count # of times "text" is in 1 column is specific date is in the other column Office 2010 64bit
Novice
Count # of times "text" is in 1 column is specific date is in the other column
 
Join Date: Dec 2016
Posts: 2
ann.acornacchio is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
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

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?
Attached Files
File Type: xlsx 2017 Orientation Example.xlsx (21.8 KB, 7 views)
Reply With Quote
  #4  
Old 12-31-2016, 07:41 AM
NoSparks NoSparks is offline Count # of times "text" is in 1 column is specific date is in the other column Windows 7 64bit Count # of times "text" is in 1 column is specific date is in the other column Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 803
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

Try this inTabulation sheet B2
Code:
=SUMPRODUCT((Table4[Campus]="NB")*(MONTH(Table4[Oritentation Start Date])=1))
Reply With Quote
  #5  
Old 12-31-2016, 05:40 PM
Berny Berny is offline Count # of times "text" is in 1 column is specific date is in the other column Windows 7 64bit Count # of times "text" is in 1 column is specific date is in the other column Office 2007
Novice
 
Join Date: Dec 2016
Posts: 3
Berny is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
countif, tabulate

Thread Tools
Display Modes


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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 04:41 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft