Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria in Functions (Excel 98 thru 2002)

    I cannot find a good explanation of how to specify the criteria in a function. For example, SUMIF allows criteria like 34, "<20". OK, that's fine for simple things but I find that most things don't stay simple for long! I'd like to be able to use other functions in the criteria. Is there a wildcard or something to use to make this work? something like SUMIF(a4:a435,isblank(??),e4:e435)? I have run into this before and have not yet found an answer. I'm hoping one of you wise folks can help. By the way, what is the best way to check if a cell is empty? I find that IF(TRIM(cell)="",... works but how could I use that in the SUMIF function??????
    Thanks for any help you can provide.

  2. #2
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria in Functions (Excel 98 thru 2002)

    Use nested SUM and IF functions if you have multiple criteria:
    http://support.microsoft.com/default.aspx?...b;en-us;Q275165

    Using an array function like this can check values against multiple criteria before summing...
    {=SUM(IF(A1:A10>=1,IF(A1:A10<=10,A1:A10,0)))}

    An array formula like this must be entered with Ctrl-Shift-Enter to get the the 'curly brackets' to appear. No curlies = no array calculation = no work. :-) Basically, the IF criteria are applied to each cell in the range and the SUM adds up all the values that pass the IFs.

    RE: criteria for blank cells
    This array formula does a count of cells with negative values or blanks:
    {=SUM(IF(A1:A10<0,1,IF(ISBLANK(A1:A10),1,0)))}
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Criteria in Functions (Excel 98 thru 2002)

    For criteria examples, look at John Walkenbach's Summing and Counting Using Multiple Criteria as well as the rest of his site j-walk.com. His book, Excel 2002 Formulas, probably has a great explanation, but I haven't read it.

    For empty cells, I just use "" (two double quotes). For example, if the number of contacts are in C2:C13 and the sales are in D213, then to find the total sales where no contact was made, assuming the cell is left empty if there is no contact, is<pre>=SUMIF(C2:C13,"",D213)</pre>

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria in Functions (Excel 98 thru 2002)

    Wow! Great Help! Thanks so much to both of you!! I will try several of these suggestions right away. I feel better all ready!
    Thanks

  5. #5
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Criteria in Functions (Excel 98 thru 2002)

    =if(FeelBetter("You"),FeelBetter("Me"), " <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>")
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria in Functions (Excel 98 thru 2002)

    Counting and summing depending on a single condition should be done with COUNTIF and SUMIF, respectively. Both accept constants like 7, "B", a cell housing a constant, or a computation that evaluates to a scalar value. Examples:

    =COUNTIF(A1:A10,MAX(A1:A10)), where the MAX bit evaluates to a scalar;

    =SUMIF(A1:A10,">="&AVERAGE(A1:A10),B1:B10), where the AVERAGE bit also evaluates to a scalar.

    For counting and summing depending on multiple conditions, you need to devise either (a) SUMPRODUCT or array-formulas or ([img]/forums/images/smilies/cool.gif[/img] formulas with database functions.

    For (a), see also:

    http://www.mrexcel.com/wwwboard/messages/8961.html

    Aladin
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •