+ Reply to Thread
Results 1 to 10 of 10

Trying to count the number of cells that meet two sets of Criteria

  1. #1
    Registered User
    Join Date
    09-21-2013
    Location
    Nelson, BC
    MS-Off Ver
    Excel 2007
    Posts
    8

    Trying to count the number of cells that meet two sets of Criteria

    So I'm trying to fill in the last column.

    I need to know how many values (in the second column) in each year exceed number in the 80% column. I have a long list of dates going to 2013 or so.


    Year Value Date
    1957 467 01/10/1957 Year 80% of Max Number of Days when Value is greater than 80% of Max
    1957 467 02/10/1957 1957 582.4
    1957 467 03/10/1957 1958 1904
    1957 476 04/10/1957 1959 1600
    1957 487 05/10/1957 1960 3328
    1957 496 06/10/1957 1961 1472
    1957 501 07/10/1957 1962 1672
    1957 535 08/10/1957 1963 1864
    1957 541 09/10/1957 1964 1304
    1957 544 10/10/1957 1965 3080
    1957 524 11/10/1957 1966 1600
    1957 510 12/10/1957 1967 1440
    1957 510 13/10/1957 1968 1184
    1957 521 14/10/1957 1969 2224
    1957 530 15/10/1957 1970 3080
    1957 544 16/10/1957 1971 3760
    1957 561 17/10/1957 1972 2896
    1957 586 18/10/1957 1973 2008
    1957 626 19/10/1957 1974 2376
    1957 685 20/10/1957 1975 1968
    1957 728 21/10/1957 1976 1664
    1957 688 22/10/1957 1977 2240
    1957 651 23/10/1957 1978 1912
    1957 623 24/10/1957 1979 2320

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,740

    Re: Trying to count the number of cells that meet two sets of Criteria

    Hi,

    Since you still have the older XL2003 you'll need a SUMPRODUCT() function rather than the arguably simpler COUNTIFS() function that was introduced for XL 2007+

    So in F3 copied down

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


    Obviously change the ranges to cover your real data.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-21-2013
    Location
    Nelson, BC
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Trying to count the number of cells that meet two sets of Criteria

    Thanks so much Richard. That works great!!

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Trying to count the number of cells that meet two sets of Criteria

    Countifs should work with 2007 if I remember correctly.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    09-21-2013
    Location
    Nelson, BC
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Trying to count the number of cells that meet two sets of Criteria

    Thanks, super, I was wondering about the countif version.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying to count the number of cells that meet two sets of Criteria

    Quote Originally Posted by newdoverman View Post

    =COUNTIFS($A$2:$A$25,"="&D3,$B$2:$B$25,">"&E3)
    No need for the equal sign in the criteria arguments. Testing for equality is the default condition.

    =COUNTIFS($A$2:$A$25,D3,$B$2:$B$25,">"&E3)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,740

    Re: Trying to count the number of cells that meet two sets of Criteria

    Hi,

    Just wondering but did you change your profile to indicate you had XL 2007 rather than XL2003 since clearly and perhaps mistakenly I thought you only had XL2003 and hence mentioned the SUMPRODUCT would be needed rather than the COUNTIFS which I mentioned.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Trying to count the number of cells that meet two sets of Criteria

    You're correct of course Tony. I had a lot going on and just didn't take the = out before posting.

    At my age it should be "one thing at a time"

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying to count the number of cells that meet two sets of Criteria

    You're welcome. Thanks for the feedback!

  10. #10
    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: Trying to count the number of cells that meet two sets of Criteria

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED",as per our Forum Rule #9. I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    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.

+ 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] How to count cells that meet two criteria. Please Help!
    By Doolie01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-18-2013, 02:07 PM
  2. Using SUM to count cells that meet multiple criteria
    By cricket_stoner in forum Excel General
    Replies: 10
    Last Post: 05-31-2010, 06:01 AM
  3. count cells that meet criteria
    By bmunoz64 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-01-2009, 08:19 AM
  4. Count cells only if three in a row meet criteria
    By htollefson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-29-2007, 07:47 AM
  5. How can I count cells that meet two criteria within a filtered co.
    By lizzzy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2005, 02:06 PM

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