+ Reply to Thread
Results 1 to 3 of 3

Count Months that occur more than once in the data set but ignore Months that show once

  1. #1
    Registered User
    Join Date
    03-18-2014
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2007
    Posts
    19

    Count Months that occur more than once in the data set but ignore Months that show once

    Hi,
    i am wondering how to count months that occur more than once in the data set below but at the same time i need to ignore months that only occur once (e.g May and July). The formula i used =SUMPRODUCT(($C$3:$C$21<>"")/COUNTIF($C$3:$C$21,$C$3:$C$21&"")) returns number of months as 7 but it actually the correct result is 4. If you could help me with this that would be great! Thank you.
    Personnel Number Date Month Days
    Chris W 5/10/2013 May Fri
    Chris W 6/6/2013 Jun Thu
    Chris W 6/13/2013 Jun Thu
    Chris W 6/14/2013 Jun Fri
    Chris W 6/27/2013 Jun Thu
    Chris W 6/28/2013 Jun Fri
    Chris W 7/12/2013 Jul Fri
    Chris W 8/16/2013 Aug Fri
    Chris W 8/19/2013 Aug Mon
    Chris W 8/20/2013 Aug Tue
    Chris W 8/30/2013 Aug Fri
    Chris W 9/6/2013 Sep Fri
    Chris W 9/20/2013 Sep Fri
    Chris W 9/23/2013 Sep Mon
    Chris W 9/27/2013 Sep Fri
    Chris W 9/30/2013 Sep Mon
    Chris W 10/25/2013 Oct Fri
    Chris W 10/28/2013 Oct Mon
    Chris W 12/13/2013 Dec Fri
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Count Months that occur more than once in the data set but ignore Months that show onc

    Try this version

    =SUM((COUNTIF(C3:C21,TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm"))>1)+0)
    Audere est facere

  3. #3
    Registered User
    Join Date
    03-18-2014
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Count Months that occur more than once in the data set but ignore Months that show onc

    Thank you so much it worked like a charm! ..i've spent the whole day pondering with this formula...Do you have any suggestions how I can combine this formula with sumproduct so I don’t have apply this formula manually to every person on my list? I have about 500 people i have to do this analysis on all of these people. I would like to summaries their monthly counts per person so thats why i was thinking of using sumproducts. I hope this makes sense. Thank you again! You made my day!

+ 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. Ignore Months In The Future
    By xybadog in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-28-2014, 07:06 AM
  2. Using index to return data from list for last 12 months or months available
    By welchs101 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-23-2013, 09:44 AM
  3. Formula to show current and previous months data
    By dandavis1 in forum Excel General
    Replies: 4
    Last Post: 07-06-2012, 02:02 PM
  4. Months that occur between two dates
    By timbellamy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-13-2010, 11:31 AM
  5. Replies: 4
    Last Post: 06-16-2006, 11:20 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