+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS based on text and date

  1. #1
    Registered User
    Join Date
    02-06-2024
    Location
    Manchester, UK
    MS-Off Ver
    Microsoft 365 Version 2401
    Posts
    1

    COUNTIFS based on text and date

    I have a table of data with text strings. I need to count the number of instances of each text string, which I have done with a simple COUNTIF. But I also need to count them if the date in the column header is after today.

    I have been trying COUNTIFS but can't get it right. Any help is appreciated.

    Excel example.PNG
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,844

    Re: COUNTIFS based on text and date

    =SUMPRODUCT(($A$2:$BB$5=A9)*($A$1:$BB$1>TODAY()))

    but countif
    =COUNTIF($A$2:$BB$5,A9)
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,926

    Re: COUNTIFS based on text and date

    Change the formula in B9 to this:

    =COUNTIF($2:$5,A9)

    (i.e. use $ symbols in front of the row references so that they do not change). You can use this formula in cell C9:

    =SUMPRODUCT(($2:$5=A9)*($1:$1>=$A$7))

    Copy both formulae down to the bottom of your key.

    Hope this helps.

    Pete

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,926

    Re: COUNTIFS based on text and date

    Thanks for the rep, given about an hour ago.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.


    Pete

+ 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] CountIFS based on a date
    By peterderrington in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-13-2022, 05:31 AM
  2. [SOLVED] Countifs based on date and time
    By benriley94 in forum Excel General
    Replies: 7
    Last Post: 09-27-2022, 09:41 AM
  3. Count/CountIFS with Date and Text Conditions
    By solios in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-18-2016, 12:38 PM
  4. [SOLVED] Countifs with text and date
    By Taiter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-03-2014, 10:06 PM
  5. Replies: 2
    Last Post: 02-20-2012, 05:59 PM
  6. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  7. COUNTIFS Using Date Text as Variable
    By ltsolis in forum Excel General
    Replies: 3
    Last Post: 09-08-2011, 07:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1