Solved

MS Excel counting cells that meet criteria based to two conditions

Posted on 2013-01-12
5
214 Views
Last Modified: 2013-01-12
Hi all,
I wonder if you could help me.

I have two columns of data. One column 'F' has the results of a calculation the other 'I' has the results of a count of a teams results. (I've attached a simple spreadsheet that represents this but not the actual one to keep things simple.) two-criteria.xls

I want to be able to count the cells in 'I' that have 1 in and it's corresponding value of 'F' range is between 0.5 and 1 for instance. I thought I could use a COUNTIF function but for the life of me I can't get it to work.

I've deleted out my attempts from the spreadsheet.

Any assistance would be gratefully received.

Dave
0
Comment
Question by:Irazor
  • 3
5 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 200 total points
Comment Utility
You can use SUMPRODUCT:

=SUMPRODUCT(--(I:I=1),--(F:F >=0.5),--(F:F<=1))

Flyster
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well I hope you have Excel 2007 and up as in this attached file.  You use SUMIFS
gowflow
Copy-of-two-criteria.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
If you want to include both 0.5 and 1 then in the formual you would change
">0.5" to ">=0.5" and
"<1" to "<=1"

gowflow
0
 

Author Closing Comment

by:Irazor
Comment Utility
This is excellent as it gives the result in one cell. Thanks.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Sorry hv u tried my solution ?
gowflow
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now