+ Reply to Thread
Results 1 to 7 of 7

Count a row if one of many cells contains a value greater than 0

  1. #1
    Registered User
    Join Date
    12-15-2014
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 2013
    Posts
    3

    Count a row if one of many cells contains a value greater than 0

    In an excel document I have a series of data values split into columns (let us use columns A-F for simplicity). Each row represents a new person to count.

    I would like excel to count a person if any of the values in their row in columns A through to F is greater than 0. Regardless of the number in any column, if it fulfills the criteria, I only want it to count the person once.

    I have tried to use COUNTIF and COUNTIFs functions but I can't seem to get the formula to resolve properly.

    To make this clear, I am trying to create a formula that: Counts the number of rows where one or more cells in columns A-F has a value greater than 0.

    Thanks for the assistance!

  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: Count a row if one of many cells contains a value greater than 0

    One way...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    1
    0
    21
    0
    40
    0
    0
    Count
    2
    0
    0
    0
    0
    0
    0
    4
    3
    0
    0
    0
    0
    0
    0
    4
    0
    0
    64
    0
    0
    0
    5
    0
    0
    0
    0
    0
    0
    6
    0
    26
    0
    0
    0
    0
    7
    0
    0
    0
    0
    0
    0
    8
    0
    0
    0
    0
    0
    0
    9
    0
    0
    0
    24
    0
    0
    10
    0
    0
    0
    0
    0
    0
    11
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------


    This formula entered in H2:

    =SUMPRODUCT(--(MMULT(--(A1:F10>0),{1;1;1;1;1;1})>0))

    The array of 1s: {1;1;1;1;1;1}, refers to the number of columns in the range.

    If you have "many" columns you wouldn't necessarily want to use the array constant. If that's the case just let me know and we'll do it a different way.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Count a row if one of many cells contains a value greater than 0

    One easy way would be to create a sum of A-f in column G then simply =countif(G:G,">0")

    Edit: this assumes there are no negative values in A-F
    Happy with my advice? Click on the * reputation button below

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Count a row if one of many cells contains a value greater than 0

    Try this one in H3

    =SUM(SUM(A:A)>0,SUM(B:B)>0,SUM(C:C)>0,SUM(D:D)>0,SUM(E:E)>0,SUM(F:F)>0)
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Count a row if one of many cells contains a value greater than 0

    Marvin

    do you mean

    =SUM(SUM(1:1)>0,SUM(2:2)>0,SUM(3:3)>0,SUM(4:4)>0,SUM(5:5)>0,SUM(6:6)>0)

    i think he wants to count the rows

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

    Re: Count a row if one of many cells contains a value greater than 0

    Quote Originally Posted by Crooza View Post

    =SUM(SUM(1:1)>0,SUM(2:2)>0,SUM(3:3)>0,SUM(4:4)>0,SUM(5:5)>0,SUM(6:6)>0)
    Just hope they don't have 100s or 1000s of rows of data!

  7. #7
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Count a row if one of many cells contains a value greater than 0

    Quote Originally Posted by Tony Valko View Post
    Just hope they don't have 100s or 1000s of rows of data!


    Yep I was thinking the same thing. While the purists won't like the idea of a helper column I reckon my original suggestion in post #3 is reasonably simple and will work for multiple rows.

+ 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] Count cells containing Dates (greater than 2014) based on Filtered Cells
    By sam99 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-26-2014, 10:30 AM
  2. [SOLVED] How to count cells after last number greater than 0
    By gogreenpower in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-09-2014, 02:28 AM
  3. Count cells (not value) greater than 0
    By rwhite713 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2013, 10:41 PM
  4. [SOLVED] Using Countif to count cells greater than zero in a macro
    By cashmaninc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-26-2013, 11:15 AM
  5. Count non-contiguous cells where value is greater than 0
    By anre5180 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-22-2006, 09:12 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