+ Reply to Thread
Results 1 to 4 of 4

COUNTIF when filter applied to table.

  1. #1
    Registered User
    Join Date
    01-10-2020
    Location
    Portland
    MS-Off Ver
    365
    Posts
    4

    COUNTIF when filter applied to table.

    I'm trying to write my formula to update my totals when I apply a filter to the column.

    Currently I'm using COUNTIF($B$17:$B$1000,"1")

    "1" in this instance is a name and NOT intended to be a value. I need the Sum to land in B3.

    I've been trying to use =SUMPRODUCT with no success. Unfortunately, this is a new formula for me.

    =SUMPRODUCT(SUBTOTAL(3,OFFSET($B$17:$B$1000,ROW($B$17:$B$1000)-ROW($B$17),0,1)),--($B$17:$B$1000="1"))

    When using this formula it returns a total of "0"

    Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: COUNTIF when filter applied to table.

    How about
    =SUMPRODUCT((Append1[Location]=[@Zone])*(SUBTOTAL(3,OFFSET($B$17,ROW(Append1[Location])-MIN(ROW(Append1[Location])),0))))

  3. #3
    Registered User
    Join Date
    01-10-2020
    Location
    Portland
    MS-Off Ver
    365
    Posts
    4

    Re: COUNTIF when filter applied to table.

    This formula seems to work but it zeros out the Totals in Row 13 for some reason.

    Thanks so much for your help!

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: COUNTIF when filter applied to table.

    Didn't notice that & not sure why it's doing it.

+ 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: 1
    Last Post: 11-14-2018, 09:40 AM
  2. Countif Formula AFTER Table Filter Data
    By Camel in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-26-2017, 03:28 AM
  3. [SOLVED] Checking if filter is applied
    By Niclal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2014, 07:48 AM
  4. Replies: 0
    Last Post: 02-28-2014, 04:49 PM
  5. Pivot Table - Advanced Filter or Countif in Excel 2007
    By Andjsmith in forum Excel General
    Replies: 1
    Last Post: 07-17-2012, 10:59 AM
  6. How to insert a row by hitting tab with filter applied in a table
    By cingozyerebatan in forum Excel General
    Replies: 1
    Last Post: 09-22-2010, 12:36 PM
  7. [SOLVED] how do i identify first and last row once filter applied
    By pete the greek in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-21-2006, 12: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