+ Reply to Thread
Results 1 to 7 of 7

Count entries with 3 or more conditions

  1. #1
    Registered User
    Join Date
    10-21-2013
    Location
    Bangalore India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Count entries with 3 or more conditions

    I have a sheet which lists out the percentage of access for different fields , and whether or not a field is active. For example:

    Access Percent Active
    22 yes
    55 no
    97 no
    12 yes
    87 yes
    44 yes
    65 yes

    now I need to get the results for the following:

    Number of fields which are active and have access percentage between 35 and 70. For example in this case the answer should be 2.

    I have tried using COUNT IF, SUMPRODUCT...and several combinations thereof, all to no avail. Please help

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Count entries with 3 or more conditions

    Try this…

    =SUMPRODUCT((A1:A7>=35)*(A1:A7<=70)*(B1:B7>="Yes"))


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Count entries with 3 or more conditions

    welcome to the forum bk1983. try:
    =SUMPRODUCT((B2:B8="yes")*(A2:A8>=35)*(A2:A8<=70))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count entries with 3 or more conditions

    Hi

    Try this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,896

    Re: Count entries with 3 or more conditions

    Hi and welcome to the forum

    If you do have 2003 (as your profile says), use the sumproduct() suggested above. If you have 2007 or later (pleas update your profile), you could look at using the COUNTIFS() function

    =COUNTIFS(A2:A20,">=35",A2:A20,"<=70",B2:B20,"yes")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    10-21-2013
    Location
    Bangalore India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Count entries with 3 or more conditions

    Actually I had tried it before, but hadn't worked. Weird. Guess I didn't put the second set of brackets. Thanks to you both guys

  7. #7
    Registered User
    Join Date
    10-21-2013
    Location
    Bangalore India
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Count entries with 3 or more conditions

    Thank. That has helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Consolidate rows with duplicate entries into one row based on some conditions...
    By lifeisaspreadsheet in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 11-04-2012, 12:24 PM
  2. Count unique entries in column with predetermined conditions
    By Eetupelle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2012, 03:11 PM
  3. Replies: 6
    Last Post: 01-17-2010, 07:30 PM
  4. vlookup with conditions to find multiple entries
    By excelnerd1 in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 02-05-2009, 05:56 PM
  5. [SOLVED] Unique Entries with Conditions
    By Rif in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-02-2006, 02:05 AM

Tags for this Thread

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