1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: per month how many specific criteria occurs

Discussion in 'Business Applications' started by joannelittell, Apr 24, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. joannelittell

    joannelittell Thread Starter

    Joined:
    Apr 24, 2013
    Messages:
    10
    Each name can have various actions on various dates. If a name's action is "no action"at least once, it should be counted.
    Based on this, I need to count each of these per month.

    It must also account for newly added data (so pivot tables isnt very convienent).

    For the data below:
    Feb= 4
    Mar= 0
    Apr= 1


    date...............name.......action taken
    02/25/2013.....aaa...........no action
    02/25/2013.....aaa...........smile
    02/05/2013.....bbb...........jump
    02/05/2013.....bbb..........walk
    02/05/2013.....bbb..........no action
    02/12/2013.....ccc...........sit
    02/25/2013.....ddd..........no action
    03/25/2013.....eee..........no action
    03/25/2013.....eee..........no action
    04/25/2013.....fff............walk
    04/25/2013.....fff............no action
    02/08/2013.....ggg..........no action
    02/22/2013.....hhh..........sit
    02/22/2013.....hhh..........jump
     
  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi Joanne

    Before we dismiss pivots as a solution ... if the only problem with pivots is that it does not add new data then this is easily remedied in one of 2 ways.

    The right way ...
    define your data as a Table - highlight your table and Insert/Table - then when you add records the table will automatically expand to include the new data. Then when creating the Pivot refer to the table instead of a range of cells.

    The lazy way ...
    define your pivot with a range that is much longer than your actual data i.e. if the data is currently in A1 to C50 then define your pivot referencing A1 to C1000 - and new data will be included in the pivot.

    Let me know if there are other reasons a pivot will not suit and I can come up with some formula to get what you need.
     
  3. joannelittell

    joannelittell Thread Starter

    Joined:
    Apr 24, 2013
    Messages:
    10

    i have never used a pivot table and this document will be used by other people who will just need to open it up and have the metric results for them without doing anything.
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Ok, give me a while to come up with an alternate solution - someone else may chime in in the meantime - so hang on for a response.
     
  5. joannelittell

    joannelittell Thread Starter

    Joined:
    Apr 24, 2013
    Messages:
    10
    thanks, its been driving me crazy for days
     
  6. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    why is March=0 in your example?

    Code:
    [B]date...............name.......action taken[/B]
    [COLOR=red]03/25/2013.....eee..........no action[/COLOR]
    [COLOR=red] 03/25/2013.....eee..........no action[/COLOR]
     
    Assuming this is an oversight here is a (not very elegant) solution......

    Assuming those three columns are in A to C this solution will require you to either change the format of your dates to just show the month in the format MMM or create a new column A where you will calculate the month e.g.

    =TEXT(B2,"MMM")

    Then you need to set up a table or your results where you will have the Months listed and as the data fills up the results will be calculated.

    I've attached a spreadsheet with how this will look giving you 2 options for the results table - one vertical and one horizontal. Take a look at this and come back with any questions. If you are having trouble translating this to you worksheet it would be helpful to upload a copy in your next post.
     

    Attached Files:

  7. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    I don't understand your example. You say:

    If a name's action is "no action"at least once, it should be counted.

    But eee has "no action" twice in March, yet you say the result for March should be 0. Can you clarify?
     
  8. joannelittell

    joannelittell Thread Starter

    Joined:
    Apr 24, 2013
    Messages:
    10
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1097026

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice