Results 1 to 9 of 9

Thread: Multi-conditional Count

  1. #1

    Multi-conditional Count



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi and thanks in advance,

    I am using Excel 2003.

    Looking for a formula that will count rows that meet one condition in a column and two NOT conditions in another column.

    So something like:

    Count the number of cells in column A that contain "Completed" where the corresponding row in column B does not contain "First" or "Last"


    Thanks

  2. #2
    Someone suggested the following formula which works perfectly ...


    =SUMPRODUCT(--($A$2:$A$16="Completed"),--($B$2:$B$16<>"First"),--($B$2:$B$16<>"Last"))

  3. #3
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    33
    Articles
    0
    Quote Originally Posted by deutz View Post
    Someone suggested the following formula which works perfectly ...


    =SUMPRODUCT(--($A$2:$A$16="Completed"),--($B$2:$B$16<>"First"),--($B$2:$B$16<>"Last"))
    Actually it is good answer. There other alternative but i think yours is the best. Here is mine: It is an array formula
    =sum(((a2:a16)="Completed")*((b2:b16)<>"First")*((b2:b16)<>"Last")) CSE

  4. #4
    Thanks eisayev,

    I try to steer clear of array formulas if possible but yours would do the job also.

  5. #5
    Why not use COUNTIFS, much more efficient than SUMPRODUCT

    =COUNTIFS(A2:A16,"Completed",B2:B16,"<>First",B2:B16,"<>Last")

  6. #6
    I would use COUNTIFS but I'm still back in the dark ages with Excel 2003 and I think COUNTIFS only appeared in EXCEL 2007.

  7. #7
    Acolyte eisayev's Avatar
    Join Date
    Oct 2012
    Location
    Baku, Azerbaijan
    Posts
    33
    Articles
    0
    Quote Originally Posted by deutz View Post
    I would use COUNTIFS but I'm still back in the dark ages with Excel 2003 and I think COUNTIFS only appeared in EXCEL 2007.
    That is right. Countif (s), iferror, sumifs functions are 2007 and above excel functions

  8. #8
    What reason do you "Steer clear of Array Formulas"!!!

    Quote Originally Posted by deutz View Post
    Thanks eisayev,

    I try to steer clear of array formulas if possible but yours would do the job also.

  9. #9
    Hi Kev@Radstock,

    Nothing against array formulas really except that they are easy to break if someone tabs in and out of the cell and then you have to do the Ctrl + Shift + Enter over again. I just find it simpler to avoid them unless there is no other or better way to do something.

Posting Permissions

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