+ Reply to Thread
Results 1 to 8 of 8

Count if formula that works like sumif

  1. #1
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Count if formula that works like sumif

    Hi, Having some formula problems again...Maybe someone could help me out?

    This formula below works to sum the numbers in Column "G" Can someone advise how to count the numbers?

    Please Login or Register  to view this content.
    I tried this formula but it's not right.

    Please Login or Register  to view this content.
    Thanks as always. Mike
    Last edited by realniceguy5000; 11-29-2010 at 05:01 PM.

  2. #2
    Forum Contributor
    Join Date
    11-25-2010
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    120

    Re: Count if formula that works like sumif

    Hi there,

    You can use:

    =Count(G5:G2500)

    It will only count the numbers in the range.
    If the post was helpful please click the black star on the bottom left to add some reputation and mark your thread as SOLVED.

    A day with nothing new achieved or learned, albeit however small, is a day lost forever?

    Constant Never Ending Improvement

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Count if formula that works like sumif

    Maybe =countblank(L2:L2500) ?

  4. #4
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Count if formula that works like sumif

    Quote Originally Posted by Lifesigns View Post
    Hi there,

    You can use:

    =Count(G5:G2500)

    It will only count the numbers in the range.
    True this will count all the numbers but I dont want them counted if (L5:L2500) has a "Yes" in one of the cells.

    For Example:
    G5 = 22 and L5 =
    G6 = 8 and L6 =
    G7 = 7 and L7 = Yes

    So the result should be 2 because L7 has a Yes

    Make any sense ?

    Thanks for helping, Mike

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Count if formula that works like sumif

    Did you try my suggestion?

  6. #6
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Count if formula that works like sumif

    Quote Originally Posted by arthurbr View Post
    Maybe =countblank(L2:L2500) ?
    Ok tried this as well, the problem is that it counts all the cells that are blank so using my example above I get this:

    G5 = 22 and L5 =
    G6 = 8 and L6 =
    G7 = 7 and L7 = Yes

    With the =countblank(L5:L2500) I get 2495 Which should be 2499 all the cells in this range are blank except L7? that is another issue I suppose.

    Anyway I'm looking to count range g5:g2500 if L5:L2500 doesn't have a "Yes" in the cell
    So the result should be 2 because L7 has a Yes if using the example?

    Thank You,

  7. #7
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Count if formula that works like sumif

    With the =countblank(L5:L2500) I get 2495 Which should be 2499 all the cells in this range are blank except L7? that is another issue I suppose.
    Between L5 and L 2500 you have 2496 cells, minus 1 containing a value which gives correctly 2495

    For your problem try
    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Count if formula that works like sumif

    Quote Originally Posted by arthurbr View Post
    Between L5 and L 2500 you have 2496 cells, minus 1 containing a value which gives correctly 2495

    For your problem try
    Please Login or Register  to view this content.


    Wow, now that works perfect...Thanks a lot for your help. Mike

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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