# Output Conditional Logic Count to Cell

1. ## Output Conditional Logic Count to Cell

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.  Register To Reply

2. ## Re: Output Conditional Logic Count to Cell

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 I J 8 32 3 9 45 10 19 11 31 12 4  Register To Reply

3. ## Re: Output Conditional Logic Count to Cell

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.  Register To Reply

4. ## Re: Output Conditional Logic Count to Cell Originally Posted by endly 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.  Register To Reply

5. ## Re: Output Conditional Logic Count to Cell

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.  Register To Reply

6. ## Re: Output Conditional Logic Count to Cell

Here is an excellent tutorial for you.

then select link "AND and OR operators in Excel array formulas"  Register To Reply

7. ## Re: Output Conditional Logic Count to Cell

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)  Register To Reply

8. ## Re: Output Conditional Logic Count to Cell

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  Register To Reply