+ Reply to Thread
Results 1 to 5 of 5

Countifs - date column by month & second column text value

  1. 05-27-2016, 04:06 PM #1
    Chelrie
    Chelrie is offline
    Registered User
    Join Date
    08-20-2009
    Location
    Richmond, VA
    MS-Off Ver
    Microsoft Office 365 / Office 2016
    Posts
    5

    Countifs - date column by month & second column text value

    On the first tab "Design Log":

    Column G holds text identifying who is submitting a request e.g. "Michelle/Kathy" or "Rick/Amira" (these pull from a dropdown list so they will remain constant)
    Column K holds a date value identifying when the request was submitted

    On the second tab "Summary":

    A B
    Michelle/Kathy
    Rick/Amira

    I need B1 to count the number of requests (valued by an entry on 'Design Log) submitted by Michelle/Kathy in the month of January, current year only.
    I need B2 to count the number of requests (valued by an entry on 'Design Log) submitted by Rick/Amira in the month of January, current year only.

    I would rather not add columns to count values - I just want a formula for column B. This is a large spreadsheet - I don't want it to get bogged down anymore than necessary.
    Any help would be appreciated.

    Thanks, Michelle
    Reply With QuoteRegister To Reply
  2. 05-27-2016, 04:19 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Countifs - date column by month & second column text value

    Something like this...

    =COUNTIFS('Design Log'!G:G,A2,'Design Log'!K:K,">="&DATE(2016,1,1),'Design Log'!K:K,"<="&DATE(2016,1,31))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 05-27-2016, 04:25 PM #3
    Chelrie
    Chelrie is offline
    Registered User
    Join Date
    08-20-2009
    Location
    Richmond, VA
    MS-Off Ver
    Microsoft Office 365 / Office 2016
    Posts
    5

    Re: Countifs - date column by month & second column text value

    Perfect. Thank you very much.
    Reply With QuoteRegister To Reply
  4. 05-27-2016, 04:28 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Countifs - date column by month & second column text value

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
  5. 05-27-2016, 04:33 PM #5
    loginjmor
    loginjmor is offline
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Countifs - date column by month & second column text value

    Hi -

    It's a little hard without a sample spreadsheet to work with, but I think something like this would work:

    =COUNTIFS($G$1:$G$10,A1,$K$1:$K$10,">"&$C$1,$K$1:$K$10,"<="&$D$1)

    So assuming your names are in G1:G10 and your dates are in K1:K10 and the names you want to sum on are in column A, and the date range you want to include is in C1 and D1 (e.g., Jan 1, 2016 in C1 and Jan 31, 2016 in D1). Copy this into B2 and copy down.

    Hope this helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
    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. CONCATENATE Day column, month column, and year column to make a date column
    By prjroberts in forum Excel General
    Replies: 7
    Last Post: 08-09-2015, 05:07 PM
  2. [SOLVED] Combine date (month) and text and still be able to add a month to next column
    By kinscy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-20-2014, 12:34 AM
  3. column B will automatically populate the month depending on the date in column A
    By Elainefish in forum Excel General
    Replies: 1
    Last Post: 04-10-2014, 04:29 AM
  4. [SOLVED] Sumproduct. count how many time a month appears (in column filled by date or text). #Value
    By adriendriver in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2013, 08:43 AM
  5. [SOLVED] [COLUMN]Match Month and Year of date Only, [ROW] row that has Text "Total" on Column A
    By ron2k_1 in forum Excel General
    Replies: 3
    Last Post: 01-17-2013, 07:20 PM
  6. how do i create a column to show the month only using the date column as a reference
    By Monz in forum Excel General
    Replies: 5
    Last Post: 01-16-2013, 08:42 PM
  7. CountIfs By Month - Multiple Date and Text Conditions
    By MAC0605 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-08-2013, 06:22 PM

Tags for this Thread

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