+ Reply to Thread
Results 1 to 6 of 6

I need to show the number of occurances of each cell in a given month

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Cape Girardeau, MO
    MS-Off Ver
    Excel 2007
    Posts
    18

    I need to show the number of occurances of each cell in a given month

    In the attached workbook, I need to show the number of occurances in the tab marked incident type.


    There is a formula that is listed there that I am not familiar with that I would hoped would work by modifing it but alas it did not.

    I need to show the number of occurances that has happenened with the incident type in their respected months under the incident type tab.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: I need to show the number of occurances of each cell in a given month

    There should be a sheet for each month from which to extract the data: sheet names are "Jan", "Feb" etc with Incident type in Column F

    =COUNTIFS(INDIRECT("'"&D$1&"'!$F:$F"),"="&$A2)

    D$1 refers to the Sheet name e,g. "JAN", data is in colunmn F and matching data in column A of "Incident Type"
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: I need to show the number of occurances of each cell in a given month

    Do you have any issues with using a pivot table?

    First fix your date, if you must add a column next to it that is =Value(Incident Start Date) so that you get the actual date and not a text version. If you make the Value field the A column so you can keep adding to the log without moving data that will be best case. From there make this thing a table CTRL+T should auto highlight the data if you are in it or highlight the data and make it a table. Then go to the insert tab and make it a pivot table.

    From there you will want to drop the Date field that is values of the date into rows, Right click on the data that shows and GROUP to Months. Now move the months reference from Rows to Columns.

    Next drop the code: Description into Rows and again into VALUES . This will give you the break down that you appear to be making on your Unit Occurrence tab.

    Now when you add to the MAIN sheet, you can go back to the pivot and hit refresh and it will automatically expand around it.

    NOTE - if you plan on letting this go for years you can do a couple things

    When you group, group to MONTHS AND YEARS. Then you have the option to display the YEARS above/below the code.
    - Above will make it where the YEARS are the top level grouping and when expanded you will see all codes within that year
    - Below will make the CODE the top level grouping and below each code will be each year that they appear

    both of those options keep the column width under control... but if you want to see a side by side you can put years above months on column so that it shows them in chronological order and will expand as far as your data goes in dates...

    Are you needing it to be individual tabs like you have it? Feels like a lot of work... That is just redundant for the author

    ----

    Now to incorporate the status/offences you can drop the result below the code where Year was suggested above and it will list all the results that took place respective to that code and give you the respective counts without putting anything else into Values.

    Or you can remove code and make a second pivot the same structure as code was just based on the statuses.... all choices on what you want to see...but play with that a bit to see if this is a solution. It becomes light weight, less moving parts/formulas and you dont have to worry about your index (Distinct lists of codes and statuses) falling out of sync with new entries over time!


    --Fix the date field on MAIN
    --Make the MAIN data a table, Name the Table MAIN
    --Make one or two pivots off the MAIN table

    Pivot 1 : Count of Codes By Month
    ---- Rows CODE:Description
    ---- Columns Date (Grouped to MONTH)
    ---- Values CODE:Description


    Pivot 2 : Count of Statuses/Offences By Month
    ---- Rows Statuses/Offences
    ---- Columns Date (Grouped to MONTH)
    ---- Values Statuses/Offences


    Pivot 3 : COMBO : Count of Codes| Statuses/Offences By Month
    ---- Rows CODE:Description
    --------------------- Statuses/Offences
    ---- Columns Date (Grouped to MONTH)
    ---- Values CODE:Description
    -If you think you are done, Start over - ELeGault

  4. #4
    Registered User
    Join Date
    04-09-2013
    Location
    Cape Girardeau, MO
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: I need to show the number of occurances of each cell in a given month

    John,

    Yes, that method works for the cad units, in column f.

    OK, Sorry, I did not follow the instruction on how th formula worked in the first one. Like I stated before, I have not ever used that formula and I need to read more about it. I changed the !$F:$F to !$D:$D and now I have the results!

    Excellent explanation of the formula my good Sir.

    Slainte.

  5. #5
    Registered User
    Join Date
    04-09-2013
    Location
    Cape Girardeau, MO
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: I need to show the number of occurances of each cell in a given month

    ELeGault, that would be a great idea for a future project that I would incorporate. I would be able to use graphs as well on the pivot table to show the elevation and fall for each instance.

    Thank you all!

  6. #6
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: I need to show the number of occurances of each cell in a given month

    No worries. I wouldnt say that it is a future need as much as it will help you now.

    See the attached file.

    You will find that it can deliver the view you are attempting to do with formulas.

    Those formulas will require you to constantly keep lists up to date... can do this dynamically without a pivot... Similar to how I built the MonthReview tab.

    Instead of having many sheets with repeated data in identic layouts, you can maintain a single repository which you are doing with MAIN and bring over the month of items you wish to review... or whatever other triggers you wish to use...

    Can toss it aside if its not for you, but I feel that this is the better setup for what you are attempting to maintain... much less overhead...less management for the author (you).

    Cheers
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] I need to show the number of occurances of each unit in a given month
    By lex.luthor in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-28-2022, 02:36 PM
  2. Replies: 3
    Last Post: 07-02-2019, 08:55 AM
  3. Replies: 1
    Last Post: 11-19-2014, 06:08 PM
  4. [SOLVED] VBA to find text and show the number of occurances in msg box
    By shiva_reshs in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-01-2013, 09:17 AM
  5. [SOLVED] display cell to show only Month/year to do sumif calc if today()= any day of month
    By fireguy7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2013, 06:55 PM
  6. Show week number in current month
    By DKerr in forum Excel General
    Replies: 4
    Last Post: 02-23-2006, 05:25 PM
  7. [SOLVED] show month number as month name in Excel?
    By Phil Hart in forum Excel General
    Replies: 3
    Last Post: 06-20-2005, 02:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1