+ Reply to Thread
Results 1 to 5 of 5

How to Countif filtered data

  1. #1
    Registered User
    Join Date
    01-19-2011
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    76

    How to Countif filtered data

    Hi all

    I have excel file. I want to count data in filter with no duplicate data.

    for example: SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B44,ROW(B2:B44)-MIN(ROW(B2:B44)),,1)),ISNUMBER(SEARCH("2010 - Q2",B2:B44))+0)
    But it still count all data in that colume by duplicate data.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: How to Countif filtered data

    Hi

    E4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This is array formula. Press same time (Ctrl+Shift+Enter) till you see both end{}. DO NOT PRESS ENTER ON IT OWN? Otherwise it won't work
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  3. #3
    Registered User
    Join Date
    01-19-2011
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: How to Countif filtered data

    Hi micope21,
    thank you so much ,
    so how can I count all data in column B with no duplicates data.
    for my example the result should be by 4

  4. #4
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: How to Countif filtered data

    Hi

    This will count 4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This is array formula. Press same time (Ctrl+Shift+Enter) till you see both end{}. DO NOT PRESS ENTER ON IT OWN? Otherwise it won't work

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,175

    Re: How to Countif filtered data

    Quote Originally Posted by thaimic View Post
    for my example the result should be by 4
    or try this...
    =SUMPRODUCT((B6:B45<>"")/COUNTIF(B6:B45,B6:B45&""))

+ 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. Replies: 3
    Last Post: 11-04-2013, 07:50 PM
  2. countif greater than on filtered data, then copy the result
    By extremis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 01:48 PM
  3. Replies: 4
    Last Post: 09-04-2012, 06:09 AM
  4. Countif on filtered data - how does it work?
    By kramerica in forum Excel General
    Replies: 1
    Last Post: 07-22-2012, 07:45 AM
  5. [SOLVED] Countif on filtered rows
    By djcfisher in forum Excel General
    Replies: 4
    Last Post: 05-21-2012, 04:25 PM
  6. Help With Countif filtered list
    By alwilly45 in forum Excel General
    Replies: 1
    Last Post: 09-12-2011, 12:46 PM
  7. CountIf in Filtered Table
    By cityofnr in forum Excel General
    Replies: 4
    Last Post: 01-06-2011, 10:54 AM
  8. [SOLVED] CountIF on Filtered data
    By Steadman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2005, 07:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1