Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Unanswered: Countif, Multiple Colums with another Countif per column!

    Hi there,

    I have been using the following formula successfully for counting instances in rows that match all 3 criteria (spread across 3 columns):


    =SUMPRODUCT(--(H120:H126="R02"),--(I120:I126="NO"),--(J120:J126="Lol"))


    So this formula tells you how many rows contain "R02" AND "NO" AND "Lol" in the required columns and ignores anything which does not contain ALL 3

    What if my "Lol" criteria was changed to a COUNTIF argument? Instead of looking at "Lol" as my criteria, I only want to look at cells which contain ANY value and ignore blank cells. this would be =COUNTIF(AH2:AH129,">0")




    So Im looking to substitue my third criteria which was set to "Lol" for a countif greater than 0 argument.


    This would give me a count on all rows which contain "R02", "NO" AND "anything at all in this third column, ignoring the blanks"


    I am struggling with this one! :P Is there an excel wizard out there who could solve this one?




    G

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why not just use this:
    Code:
    =SUMPRODUCT(--(A2:A9="R02"),--(B2:B9="NO"),--(C2:C9<>""))
    ?
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2013
    Posts
    3
    You absolute legend. That works perfectly!


    FYI - I started using excel about 3 months ago so I am just learning.




    Whilst I have an excel wizard to hand, could I ask you 1 more question?

    How can I make a cell change its value based on another Cell?


    For example, I want Cell A1 to automatically say "Yes" if cell B1 Contains ANY value.



    Graham

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    =if(b1<>"", "yes", "no")
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2013
    Posts
    3
    Quote Originally Posted by gvee View Post
    Code:
    =if(b1<>"", "yes", "no")



    Hi George,


    is it ok to say I love you?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'd expect nothing less
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    If you're using Excel 2007 or later then you can use COUNTIFS() instead of SUMPRODUCT().

    Code:
    =COUNTIFS(A2:A9,"R02",B2:B9,"NO",C2:C9,"<>")
    The general advantages of using COUNTIFS() instead of SUMPRODUCT() are that it is a simpler formula and it is quicker to calculate (in this case the calculation time difference on such a small dataset and for a single formula would be negligable).

  8. #8
    Join Date
    Aug 2013
    Posts
    2
    Hi,

    I know I must start a new thread, but somehow my excel problem is almost like this except that may column range is a date, meaning i need to validate first the date selected before running the count if.. Please help, I'm also new in creating formulas in excel. Thanks

    =COUNTIFS(Sheet1!$E:$E,"Lab 1 WS 2",Sheet1!$F:$F,sheet2!$B$2,Sheet1!$H:$AL,Sheet2$a $2) - value from Sheet2 a2 and b2 varies.. and column header for H:AL is date..please help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •