Results 1 to 7 of 7

Thread: COUNTIFS formula with INDEX/MATCH column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default COUNTIFS formula with INDEX/MATCH column

    Hi All

    =COUNTIFS(DataWork!$A$3:$A$10000,"*CDITT*",DataWork!$Q$3:$Q$10000,"*",DataWork!$P$3:$P$10000,"<"&TODAY())
    DataWork Column Q contains INDEX/MATCH formula results, am I right in thinking that is why this formula returns a count of zero?

    Many thanks

  2. #2
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS formula with INDEX/MATCH column

    I THINK THE RIGHT FORMULA MUST BE LIKE THIS
    Code:
    =COUNTIFS(DataWork!$A$3:$A$10000,"*"&"CDITT"&"*",DataWork!$Q$3:$Q$10000,"?*",DataWork!$P$3:$P$10000,"<"&TODAY())
    Last edited by salim hasan; Sep 15th, 2019 at 09:31 AM.

  3. #3
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS formula with INDEX/MATCH column

    I THINK THE RIGHT FORMULA MUST BE LIKE THIS
    1--IF DataWork!$Q$3:$Q$10000 CONTAINS TEXT

    Code:
    =COUNTIFS(DataWork!$A$3:$A$10000,"*"&"CDITT"&"*",DataWork!$Q$3:$Q$10000,"?*",DataWork!$P$3:$P$10000,"<"&TODAY())
    2--IF DataWork!$Q$3:$Q$10000 CONTAINS NUMBERS

    Code:
    
    
    Code:
    =COUNTIFS(DataWork!$A$3:$A$10000,"*"&"CDITT"&"*",DataWork!$Q$3:$Q$10000,"<="&10^9,DataWork!$P$3:$P$10000,"
    
    Last edited by salim hasan; Sep 15th, 2019 at 09:58 AM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,195
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: COUNTIFS formula with INDEX/MATCH column

    @salim hasan
    Please do not make duplicate posts.
    Also in future please do not us capital letters as that is SHOUTING and is considered rude.
    Thanks
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Sep 2018
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS formula with INDEX/MATCH column

    @salim hasan, thank you but the number solution is not working for me.
    Last edited by horizonflame; Sep 15th, 2019 at 11:18 AM.

  6. #6
    Board Regular
    Join Date
    Sep 2018
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS formula with INDEX/MATCH column

    I have found that the COUNT function will count the number of cells in column Q with matches found. Could this be incorporated into my formula at all?

    =COUNTIFS(DataWork!$A$3:$A$10000,"*CDITT*",DataWork!$Q$3:$Q$10000,(COUNT))
    Many thanks again

  7. #7
    Board Regular
    Join Date
    Dec 2013
    Posts
    102
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS formula with INDEX/MATCH column

    Quote Originally Posted by Fluff View Post
    @salim hasan
    Please do not make duplicate posts.
    Also in future please do not us capital letters as that is SHOUTING and is considered rude.
    Thanks
    I know this very well
    But I made a mistake in the first Post and I don't know how to delete it
    Last edited by salim hasan; Sep 15th, 2019 at 02:39 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •