Hello I am trying to count the numbers in a column that are >= a number and <= a number. That part seems to work but I am trying to find away that will output the result into a cell not just tell me if it is true or false. I have tried many things. Last recent attempt below to give an idea. It is probably a mess by now.
=IF(AND($I$7:$I$9>= 18,$I$7:$I$9<=34), K8,"no")
Thank you.
Try this array formula
if you still using Excel 2003
=COUNT(IF((I8:I12>=18)*(I8:I12<=34),0))
=COUNT(IF((I8:I12>=18)*(I8:I12<=34)*(I8:I12),0))
***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
or
=SUMPRODUCT((I8:I12>=18)*(I8:I12<=34))
if you using Excel 2007 and up:
=COUNTIFS(I8:I12,">="&18,I8:I12,"<="&34)
Row\Col
Last edited by AlKey; 07-11-2015 at 09:52 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Yes. It works good. Can you explain it please? I don't see how the asterisks (multiplication) fit in and how this works.
Thank you.
In the context of the array formula the asterisks will function as AND.Originally Posted by endly
If your issue has been resolved please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" by Selecting Thread Tools-> Mark thread as Solved).
I'm using Excel 2013. Are the asterisks acting as Comparison Operators or is it a simple AND. It can't be a simple AND because substituting AND doesn't work and Excel wants to add the asterisks to the AND which does work. I'm trying to translate how it is working to myself. I can not find anything online or in my books about it.
My guess is, first it compares the range for the first condition, then again for the second and third. But that is a shot in the dark.
Here is an excellent tutorial for you.
https://www.ablebits.com/office-addi...nctions-excel/
then select link "AND and OR operators in Excel array formulas"
Please update your profile if you are actually using Excel 2013.
The * acts logically like "and" but is NOT the and function.
With Excel 2013 the formula to normally use (if no compatibility issues with previous versions of Excel to be considered) is the =COUNTIFS(I8:I12,">="&18,I8:I12,"<="&34)
<---------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
Try one of these...
If you're using Excel 2007 or later:
=COUNTIFS($I$7:$I$9,">=18",$I$7:$I$9,"<=34")
Or, using cells to hold the criteria:
A1 = 18
B1 = 34
=COUNTIFS($I$7:$I$9,">="&A1,$I$7:$I$9,"<="&B1)
These will work in all versions:
=COUNTIF($I$7:$I$9,">=18")-COUNTIF($I$7:$I$9,">34")
=COUNTIF($I$7:$I$9,">="&A1)-COUNTIF($I$7:$I$9,">"&B1)
=SUMPRODUCT(--($I$7:$I$9>=18),--($I$7:$I$9<=34))
=SUMPRODUCT(--($I$7:$I$9>=A1),--($I$7:$I$9<=B1))
For info on how the SUMPRODUCT function works, see this:
http://xldynamic.com/source/xld.SUMPRODUCT.html
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks