+ Reply to Thread
Results 1 to 3 of 3

CountIfs on a Filtered list?

  1. #1
    Registered User
    Join Date
    11-09-2013
    Location
    Nowhere
    MS-Off Ver
    Excel 2013
    Posts
    1

    CountIfs on a Filtered list?

    I'm trying to count the number of positively valued cells in a list that are followed by a positively valued cell (and again and again, etc.).

    My best guess as to how I would do this is to do a CountIfS using the range with one cell removed from the bottom, and the same range, starting one cell down.

    Ex. I want to see how many times there are two adjacent positive numbers in 1, 1, -1, 1, -1

    =CountIfs(A1:A4, ">0", A2:A5, ">0")

    However, the list is filtered, which screws up my counts. My research has led me to think that the Subtotal function is the usual fix, but this does not seem to accommodate using multiple criteria (in other words, it provides count if, not count ifs). Any ideas?

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CountIfs on a Filtered list?

    Can you post some sample data and tell us what result you expect?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: CountIfs on a Filtered list?

    After a quick help search, came up with this thread : http://www.excelforum.com/excel-gene...ells-only.html in this forum (And thanks to daddylonglegs for providing the original formulas!)

    So, in your case, I came up with something like this (change ranges as needed):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    this seems to work for the quick mock-up I did, if it doesn't for you, please upload a sample with some expected answers, and what is being filtered

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

+ 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. Create a list of the cells used in a COUNTIFS formula
    By Manial in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-10-2012, 08:30 AM
  2. COUNTIFS filtered by month
    By HeathWilD in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-15-2012, 05:06 PM
  3. Update the original list with filtered list updation
    By jd_dahe in forum Excel General
    Replies: 0
    Last Post: 09-03-2012, 09:01 AM
  4. Using a list of options and countifs
    By perducci in forum Excel General
    Replies: 2
    Last Post: 01-31-2012, 06:51 PM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 AM

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