+ Reply to Thread
Results 1 to 4 of 4

COUNTIFs key words

  1. #1
    Registered User
    Join Date
    07-15-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    21

    COUNTIFs key words

    Hello

    I am trying to create a formula that counts the number of 'TRUE' cells in a particular column, based on certain criteria.

    Here, I have created some dummy portfolios. Each of the three individuals (Me, John, Simon) has a column that shows the number of breaches ('TRUE').

    In the table on the left (yellow highlight) I am trying to calculate how many 'TRUEs' there are in the column, between a certain time period (cells C5:C6).

    The idea would be that this table would update, whenever you change the dates. i.e. how many breaches in the last year, or last week, etc.

    Attachment 688132


    I have attached the dummy excel file

    Thank you in advance!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,740

    Re: COUNTIFs key words

    In C12 =COUNTIFS($H$12:$H$39,"FALSE",$E$12:$E$39,">="&C6,$E$12:$E$39,"<="&C5) and use the same concept for C13 and C14

    EDIT: Noticed I had the formula backwords. I tested for FALSE. Just change formula to "TRUE"


    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    1
    2
    Goal = count the number of breaches in a given time period
    3
    Breach = 'TRUE' (Columns K, N and Q)
    4
    5
    Start
    =TODAY()-2
    6
    End Date
    6/10/2020
    7
    8
    9
    10
    Fund Name
    Number of breaches
    11
    12
    My Portfolio
    =COUNTIFS($H$12:$H$39,"FALSE",$E$12:$E$39,">="&C6,$E$12:$E$39,"<="&C5)
    13
    John's Portfolio
    =COUNTIFS($K$12:$K$39,"FALSE",$E$12:$E$39,">="&C6,$I$12:$I$39,"<="&C5)
    14
    Simon's Portfolio
    =COUNTIFS($N$12:$N$39,"FALSE",$E$12:$E$39,">="&C6,$E$12:$E$39,"<="&C5)
    15
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    1
    2
    Goal = count the number of breaches in a given time period
    3
    Breach = 'TRUE' (Columns K, N and Q)
    4
    5
    Start
    7/22/2020
    6
    End Date
    6/10/2020
    7
    8
    9
    10
    Fund Name
    Number of breaches
    11
    12
    My Portfolio
    23
    13
    John's Portfolio
    22
    14
    Simon's Portfolio
    22
    15
    Sheet: Sheet1

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,131

    Re: COUNTIFs key words

    ONe formula in C12 then drag down:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-15-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    21

    Re: COUNTIFs key words

    Hello alansidman and bebo021999

    Both of these worked well thank you!!!

    bebo021999, do you know if it would possible to exchange the CHOOSE MATCH function, with an INDEX MATCH function in your formula?

    thanks

+ 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] exclude certain words from a COUNTIFS formula
    By R.Sloan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-26-2017, 03:40 AM
  2. Issue with sumproduct/ countifs in regards finding certain words in date range
    By airedale360 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-25-2016, 08:02 PM
  3. [SOLVED] Countifs Function, Words in a cell Criteria
    By baca25 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-29-2014, 04:50 AM
  4. [SOLVED] multiple words in my COUNTIFS formula
    By tlacloche in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-22-2014, 01:42 PM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  6. Replies: 6
    Last Post: 02-21-2009, 08:13 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