+ Reply to Thread
Results 1 to 13 of 13

countifs between 2 dates with count of another column

  1. 01-04-2014, 10:44 AM #1
    Vivien
    Vivien is offline
    Registered User
    Join Date
    01-04-2014
    Location
    North England
    MS-Off Ver
    Excel 2010
    Posts
    6

    countifs between 2 dates with count of another column

    Hi,

    I'm a new member and have found lots of the previous posts very useful thanks. But have been struggling to get my CountifS to work properly with date range from 1 column AND a count of "other than blank".

    I've been trying the following in a summary worksheet which refers to another sheet in the same file (and have another 23 other sheets to do summarise from also) i.e.

    =COUNTIFS(Viv1!A2:A200,">=01/10/13",Viv1!A2:A200,"<=31/12/13",Viv1!H2:H200,"<>")

    I've used similar formulas to count text, which work ok and I know that the three separate counts work ok, it seems to be the last criteria I think! Any help would be appreciated thanks.
    Reply With QuoteRegister To Reply
  2. 01-04-2014, 10:47 AM #2
    rcm
    rcm is offline
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: countifs between 2 dates with count of another column 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 01-04-2014, 10:53 AM #3
    Vivien
    Vivien is offline
    Registered User
    Join Date
    01-04-2014
    Location
    North England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: countifs between 2 dates with count of another column

    Thanks for quick reply rcm .. but that's not working for me ...
    Reply With QuoteRegister To Reply
  4. 01-04-2014, 12:17 PM #4
    rcm
    rcm is offline
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: countifs between 2 dates with count of another column

    what is it that you testing in the third condition?
    Reply With QuoteRegister To Reply
  5. 01-04-2014, 12:37 PM #5
    Vivien
    Vivien is offline
    Registered User
    Join Date
    01-04-2014
    Location
    North England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: countifs between 2 dates with count of another column

    I want to know the number of entries in that column (they are numbers entered manually and some are blank).
    Reply With QuoteRegister To Reply
  6. 01-04-2014, 12:47 PM #6
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,453

    Re: countifs between 2 dates with count of another column

    Quote Originally Posted by Vivien View Post
    Thanks for quick reply rcm .. but that's not working for me ...
    And what is "not working" ?
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  7. 01-04-2014, 12:51 PM #7
    Vivien
    Vivien is offline
    Registered User
    Join Date
    01-04-2014
    Location
    North England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: countifs between 2 dates with count of another column

    Hi Pepe ... rcm suggested below additional "" at the end of my formula - but result is the same as before. it should be resulting in 2 counts for that date range, but it answers 1, as if it is ignoring the last H2 range altogether.

    =COUNTIFS(Viv1!A2:A200,">=01/10/13",Viv1!A2:A200,"<=31/12/13",Viv1!H2:H200,"<>""")
    Reply With QuoteRegister To Reply
  8. 01-04-2014, 03:17 PM #8
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,453

    Re: countifs between 2 dates with count of another column

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Reply With QuoteRegister To Reply
  9. 01-04-2014, 07:39 PM #9
    Vivien
    Vivien is offline
    Registered User
    Join Date
    01-04-2014
    Location
    North England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: countifs between 2 dates with count of another column

    Test.xlsx

    Not sure if this has attached a file or not, but giving it a go.
    Reply With QuoteRegister To Reply
  10. 01-04-2014, 09:25 PM #10
    rcm
    rcm is offline
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: countifs between 2 dates with count of another column

    Do you need to count the entries for each quarter or just to check if the quarter had an entry?
    Reply With QuoteRegister To Reply
  11. 01-04-2014, 09:42 PM #11
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: countifs between 2 dates with count of another column

    Quote Originally Posted by Vivien View Post
    ...it should be resulting in 2 counts...
    Hello Vivien,

    You will not get proper answer with COUNTIFS in merged cells. It require dates in each cell, try this.

    =SUMPRODUCT(ISNUMBER(MATCH(TEXT(LOOKUP(ROW('Viv1'!A$2:A$15),ROW('Viv1'!A$2:A$15)/('Viv1'!A$2:A$15<>""),'Viv1'!A$2:A$15),"mmmyyyy"),{"Jan","Feb","Mar"}&2013,0))*('Viv1'!C$2:C$15<>""))

    This is for Q1, for Q2 change months to {"Apr","May","Jun"} and proper months for other Quarters.
    Regards,
    Haseeb Avarakkan
    __________________________________
    "Feedback is the breakfast of champions"
    Reply With QuoteRegister To Reply
  12. 01-05-2014, 08:24 AM #12
    Vivien
    Vivien is offline
    Registered User
    Join Date
    01-04-2014
    Location
    North England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: countifs between 2 dates with count of another column

    Hi Haseeb - thanks very much for that. I tried it, but it wasn't always working fully. However, I didn't know that merged cells can cause a problem. So, I have unmerged all cells and copied the dates down instead ... and hey presto - my original formula has worked! I am a very happy now.

    Thanks so much for your advice and to rcm and Pepe too - really great to have people to help when I'm working by myself with an inherited spreadsheet. Have a good day.
    Reply With QuoteRegister To Reply
  13. 01-06-2014, 05:31 AM #13
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: countifs between 2 dates with count of another column

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Countifs to count rows with a date which match a range of dates
    By pharmerjoe7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-07-2013, 06:19 AM
  2. Count unique dates in a column that are between two dates (reference cells)
    By jbellows in forum Excel General
    Replies: 1
    Last Post: 12-18-2012, 03:09 PM
  3. Excel 2007 : Using Countifs to count muliple criteris from the same column?
    By Icehockey44 in forum Excel General
    Replies: 7
    Last Post: 01-10-2012, 10:47 AM
  4. Excel 2007 : Need Countifs Formula to count dates between 2 dates,
    By CRIMEDOG in forum Excel General
    Replies: 3
    Last Post: 07-19-2010, 01:02 PM
  5. Count how many dates are in a column
    By famico78 in forum Excel General
    Replies: 2
    Last Post: 11-18-2008, 08:18 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1