+ Reply to Thread
Results 1 to 4 of 4

I Need to Count Number of Entries Based on Two Criteria

  1. #1
    Jones
    Guest

    I Need to Count Number of Entries Based on Two Criteria

    I need to count the number of entries in column B only if column J does not
    equal "1" AND column B is >3.

    Not looking for a "sum" or to "multiply", I simply need a count of the
    number of entries in column B that match these two criteria.

    I have tried combining functions through IF and COUNTIF, but have not been
    successful. Any ideas? Thanks

  2. #2
    CLR
    Guest

    Re: I Need to Count Number of Entries Based on Two Criteria

    In a helper column put this and copy down............

    =IF(AND(B1>3,J1<>1),1,"")

    Then, sum the helper column, or count the 1's whichever you
    prefer..........

    Vaya con Dios,
    Chuck, CABGx3




    "Jones" <Jones@discussions.microsoft.com> wrote in message
    news:67BD77D1-1ACB-49A1-845C-A7623FD28325@microsoft.com...
    > I need to count the number of entries in column B only if column J does

    not
    > equal "1" AND column B is >3.
    >
    > Not looking for a "sum" or to "multiply", I simply need a count of the
    > number of entries in column B that match these two criteria.
    >
    > I have tried combining functions through IF and COUNTIF, but have not been
    > successful. Any ideas? Thanks




  3. #3
    Ron de Bruin
    Guest

    Re: I Need to Count Number of Entries Based on Two Criteria

    Try this Jones

    =SUMPRODUCT((J1:J10<>1)*(B1:B10>3))


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Jones" <Jones@discussions.microsoft.com> wrote in message news:67BD77D1-1ACB-49A1-845C-A7623FD28325@microsoft.com...
    >I need to count the number of entries in column B only if column J does not
    > equal "1" AND column B is >3.
    >
    > Not looking for a "sum" or to "multiply", I simply need a count of the
    > number of entries in column B that match these two criteria.
    >
    > I have tried combining functions through IF and COUNTIF, but have not been
    > successful. Any ideas? Thanks




  4. #4
    Registered User
    Join Date
    07-13-2005
    Posts
    15

    Conditional sum

    try using an Array function (Excel Help is good for this - you could also load the 'Conditional Sum Wizard' Add-In from the Tools menu to help you write the formula )

    something like

    =COUNT(if((B1:B10>3)*(J1:J10<>1),B1:B10))

    Where * represents AND & + represents OR

    Can also replace COUNT with SUM if required.

    (note : you need to press and hold Ctrl+Shift when pressing Enter after typing the formula - this tells Excel that this is an Array Function.

    (make sure your ranges match; aviod referencing whole columns as doing this multiple times will place strain on Excels calculation cycle (noticeable on my laptop when using around 50 of these in a workbook))

    hope this helps Jones

+ 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