+ Reply to Thread
Results 1 to 8 of 8

Output Conditional Logic Count to Cell

  1. #1
    Registered User
    Join Date
    01-14-2009
    Location
    Fa, CA
    MS-Off Ver
    Excel 2013
    Posts
    53

    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. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

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

  3. #3
    Registered User
    Join Date
    01-14-2009
    Location
    Fa, CA
    MS-Off Ver
    Excel 2013
    Posts
    53

    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. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Output Conditional Logic Count to Cell

    Quote Originally Posted by endly View Post
    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.


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

  5. #5
    Registered User
    Join Date
    01-14-2009
    Location
    Fa, CA
    MS-Off Ver
    Excel 2013
    Posts
    53

    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. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Output Conditional Logic Count to Cell

    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"

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Output Conditional Logic Count to Cell

    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

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need help with logic to parse through output being returned to excel from wsh
    By wherbjr35@aol.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2014, 08:46 PM
  2. Apply Conditional Formatting Formula to Count output
    By mboukili in forum Excel General
    Replies: 7
    Last Post: 06-10-2013, 10:57 AM
  3. [SOLVED] four input single output logic problem
    By Hammer_757 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-11-2012, 02:02 PM
  4. Calculate output for the logic in the attached spreadhseet based on user input
    By tenn0228 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-02-2009, 10:45 AM
  5. Count logic question
    By ACDenver in forum Excel General
    Replies: 7
    Last Post: 08-17-2005, 12:05 PM

Bookmarks

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