+ Reply to Thread
Results 1 to 3 of 3

Counting only nonblank cells in two columns (Filtered)

  1. #1
    Registered User
    Join Date
    01-25-2021
    Location
    North Florida
    MS-Off Ver
    18.2008.12711.0
    Posts
    49

    Counting only nonblank cells in two columns (Filtered)

    Hello,
    I need a formula that will give me a count of nonblank cells in a filtered range, when cells in both columns are nonblank. I'll give an example below, and I've attached a worksheet in case what I hacked together below isn't legible. I am using =SUMPRODUCT((G2:G13>E2:E13)*(SUBTOTAL(103,OFFSET(D2,ROW(D2:D13)-MIN(ROW(G2:G13)),0)))) to count how many scores increase between Quiz 1 and Quiz 2. Basically, I need a formula that would tell me how many people were present for both Quiz 1 and Quiz 2.

    BONUS QUESTION: In some rare cases, like Tegan, someone in the dataset was present for Quiz 2 and not Quiz 1. When that happens, it reads them as an increase. In the example below, the number of increased should really be 4, but Tegan is making it a 5. Can I modify the formula above to only compare columns E and G when both are nonblank?

    Thanks!

    Phase Quiz 1 (T1) Exam A Quiz 2 (T2)
    Susan___________1____________3___________43_______________3
    Ian_____________1____________4___________74_______________5
    Vicki____________1____________3___________35 ______________
    Barbara__________1___________3____________53______________ 3
    Zoe_____________2___________2____________54_______________3
    Jamie____________2___________5___________12_______________5
    Polly_____________2___________4___________43_______________5
    Tegan____________4___________ ___________55_______________4
    Turlough__________5___________ 5___________ _______________
    Leela ____________4___________2___________67_______________2
    Sarah Jane________3___________3___________56______________
    Harry_____________4___________2___________63______________4


    # of scores increased from T1 to T2: 5
    # of people who were present at both T1 and T2: __?__
    [Must be able to filter by Phase]
    Attached Files Attached Files
    Last edited by Jarvis_Cain; 04-02-2021 at 01:38 AM. Reason: Typo

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Counting only nonblank cells in two columns (Filtereed)

    There is a stray space at E9 that needs to be removed. Your formula then gives the wrong result and can be fixed using:

    =SUMPRODUCT(($G$2:$G$13>$E$2:$E$13)*($E$2:$E$13>0)*(SUBTOTAL(103,OFFSET($D$2,ROW($D$2:$D$13)-MIN(ROW($G$2:$G$13)),0))))

    to count T1+T2s, use:

    =SUMPRODUCT(($G$2:$G$13>0)*($E$2:$E$13>0)*(SUBTOTAL(103,OFFSET($D$2,ROW($D$2:$D$13)-MIN(ROW($G$2:$G$13)),0))))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    01-25-2021
    Location
    North Florida
    MS-Off Ver
    18.2008.12711.0
    Posts
    49

    Re: Counting only nonblank cells in two columns (Filtereed)

    Sorry for the pause, I've had to set this aside for a moment. I haven't had time to run checks but it looks like your solution may have worked. I'll get back to it ASAP and let you know.
    Thanks

+ 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. [SOLVED] Counting nonblank cells
    By mellowmarshall in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-24-2013, 12:15 PM
  2. Replies: 4
    Last Post: 04-25-2013, 03:53 AM
  3. Replies: 2
    Last Post: 12-22-2011, 07:05 PM
  4. Counting Nonblank cells then listing the data!
    By protocoledu in forum Excel General
    Replies: 3
    Last Post: 11-05-2009, 12:32 PM
  5. Counting nonblank cells - 3D
    By yves in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-06-2007, 04:16 AM
  6. counting nonblank cells
    By sbrimley in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-18-2005, 06:06 AM
  7. [SOLVED] Counting nonblank and non white space cells
    By Andrew in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-08-2005, 01:06 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