+ Reply to Thread
Results 1 to 5 of 5

Counting cells by using mulitiple criteria

  1. #1
    Registered User
    Join Date
    06-29-2007
    Posts
    57

    Counting cells by using mulitiple criteria

    I am hoping someone might be able to help me here. I think I am just missing something stupid, because I can get it to work... almost. Here is my situation:

    I have a spreadsheet that has information constantly being adding to it. I want to analyze this data on a continous basis. One of the areas I want to be able analyze deals with counting the number of cells, based of from mulitiple criteria. However, I am able to get this array formula to work: {=SUM((IF(DataBase!A2:A14=B3,1,0)*(IF(DataBase!L2:L14="PASSED",1))))}, but only if the cells selected are populated.

    My question is, how do I make this formula work for an entire column that has information consently being added, or is there a better way for me to accomplish this same thing with a marco or another formula?

    Thanks in advance

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    This will make it dynamic....it counts from Row 2 to last row containing entries...

    This formula assumes text entries in columns A and L....

    Please Login or Register  to view this content.

    confirmed with CTRL+SHIFT+ENTER not just ENTER

    or

    Please Login or Register  to view this content.
    confirmed with Enter only.
    Last edited by NBVC; 06-29-2007 at 03:22 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try

    =SUMPRODUCT(--(Database!A2:A1000=B3),--(Database!L2:L1000="Passed"))

  4. #4
    Registered User
    Join Date
    06-29-2007
    Posts
    57
    Thanks... both worked well. I tried the SUMPRODUCT route before, but I just couldn't get it to work. I am sure I was just missing something there. What do the "--" mean in it?

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by jat82nd
    Thanks... both worked well. I tried the SUMPRODUCT route before, but I just couldn't get it to work. I am sure I was just missing something there. What do the "--" mean in it?
    That's a double unary which coerces the True's And False's returned by the criteria checks to 1's and 0's, respectively so that the Sumproduct() arithmetic of multiplying corresponding array elements can happen.

+ 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