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

1. ## 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. ## 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:
`Please Login or Register  to view this content.`

Obviously change the ranges to cover your real data.

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

Thanks so much Richard. That works great!!

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

Countifs should work with 2007 if I remember correctly.

Formula:
`Please Login or Register  to view this content.`

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

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

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

Originally Posted by newdoverman

=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)

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

You're welcome. Thanks for the feedback!

10. ## 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 -

Incase your issue is not solved, you can undo it as follows -

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.

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

#### 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