+ Reply to Thread
Results 1 to 13 of 13

Using Countif with filter formula

  1. 08-31-2016, 02:47 AM #1
    Mike Santuile
    Mike Santuile is offline
    Registered User
    Join Date
    03-07-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    37

    Using Countif with filter formula

    Hi,

    I have attached here a worksheet regarding my report, I'm using a countif formula in the summary where in I can count all those processed with 3 or more days but if I'm going to check the data for the month of august the Summary total is fixed. Can I ask for a formula to use regarding my report so I can pre-select if I want to have data in various months. Also, I would like to count the total number of processed seafarer.

    Thank you in advance,
    Mike
    Attached Files Attached Files
    Last edited by Mike Santuile; 08-31-2016 at 02:51 AM. Reason: forgot something
    Reply With QuoteRegister To Reply
  2. 08-31-2016, 02:58 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Using Countif with filter formula

    Try

    =SUMPRODUCT((MONTH(B5:B203)=8)*(K5:K203>2))
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 08-31-2016, 03:08 AM #3
    Mike Santuile
    Mike Santuile is offline
    Registered User
    Join Date
    03-07-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    37

    Re: Using Countif with filter formula

    HI Sir,

    Thanks for a prompt reply but base from your the formula you have sent my total number of processed contracts in 3 days or more is only "1"? the total number of processed in 3 days is 47. Just wanted the filter to work wherein if I select the month of September the out put will be contracts in 3 days or more only for the month of September.
    Reply With QuoteRegister To Reply
  4. 08-31-2016, 03:17 AM #4
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Using Countif with filter formula

    checkout attached file

    There is drop down for months and it will work according your choice of month.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 08-31-2016, 03:44 AM #5
    Mike Santuile
    Mike Santuile is offline
    Registered User
    Join Date
    03-07-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    37

    Re: Using Countif with filter formula

    I have this checked out but the total is not functioning. The formula should count all "Actual Processing days" >2. THanks again
    Reply With QuoteRegister To Reply
  6. 08-31-2016, 04:19 AM #6
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Using Countif with filter formula

    Sorry I am getting your requirement? can you please mention expected result with criteria???
    Reply With QuoteRegister To Reply
  7. 08-31-2016, 04:29 AM #7
    Mike Santuile
    Mike Santuile is offline
    Registered User
    Join Date
    03-07-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    37

    Re: Using Countif with filter formula

    Expected Results would be the total amount of Contract processed in 3 days or more will be counted in the summary base on the data given by table "Actual Processing Days". I have use the countif formula it works out fine. I wanted to know what formula can I use to have only the visible values will be counted if I ever click on the filter in "Date Received" such as the month of July. So I can work per month if I wanted to extract monthly.
    Reply With QuoteRegister To Reply
  8. 08-31-2016, 04:30 AM #8
    Mike Santuile
    Mike Santuile is offline
    Registered User
    Join Date
    03-07-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    37

    Re: Using Countif with filter formula

    Countif formula is just giving the fixed total.
    Reply With QuoteRegister To Reply
  9. 08-31-2016, 05:17 AM #9
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Using Countif with filter formula

    okay try

    =SUMPRODUCT((SUBTOTAL(103,OFFSET(K5,ROW(K5:K165)-ROW(K4)-1,0)))*(K5:K165>2))
    Reply With QuoteRegister To Reply
  10. 08-31-2016, 05:41 AM #10
    Mike Santuile
    Mike Santuile is offline
    Registered User
    Join Date
    03-07-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    37

    Red face Re: Using Countif with filter formula

    Thank you very much for the helping Sir.
    Reply With QuoteRegister To Reply
  11. 08-31-2016, 05:42 AM #11
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Using Countif with filter formula

    Happy to help you
    Reply With QuoteRegister To Reply
  12. 08-31-2016, 05:46 AM #12
    Mike Santuile
    Mike Santuile is offline
    Registered User
    Join Date
    03-07-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    37

    Re: Using Countif with filter formula

    How can I make this thread solved sorry.
    Reply With QuoteRegister To Reply
  13. 08-31-2016, 05:48 AM #13
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Using Countif with filter formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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. Countif with filter
    By Gaellus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2016, 07:19 AM
  2. COUNTIF Formula when Col has a Filter
    By hammer2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-17-2014, 03:51 AM
  3. Help Creating a formula that combines a countif statement and a date filter
    By mark.studley in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-14-2013, 04:09 AM
  4. [SOLVED] Excel doesn't filter by formula in countif?
    By virtualdark in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 05-09-2013, 06:34 AM
  5. countif & sumif formula with filter ?
    By unique1 in forum Excel General
    Replies: 2
    Last Post: 02-28-2012, 03:42 PM
  6. Filter & COUNTIF
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-13-2009, 07:37 PM
  7. [SOLVED] How do I run a filter then have my countif change?
    By dtg_denver in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-04-2005, 12:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1