Results 1 to 6 of 6

20020619, 15:34 #1
 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.

20020619, 16:24 #2
 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;enus;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 CtrlShiftEnter 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

20020619, 16:27 #3
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
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 jwalk.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>

20020619, 16:59 #4
 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

20020619, 17:31 #5
 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

20020619, 20:10 #6
 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 arrayformulas or ([img]/forums/images/smilies/cool.gif[/img] formulas with database functions.
For (a), see also:
http://www.mrexcel.com/wwwboard/messages/8961.html
AladinMicrosoft MVP  Excel