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

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

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.

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.

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.

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"

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)

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

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