Criteria in Functions (Excel 98 thru 2002)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

=if(FeelBetter(“You”),FeelBetter(“Me”), ” “)

Reply | Quote

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 D2:D13, then to find the total sales where no contact was made, assuming the cell is left empty if there is no contact, is

=SUMIF(C2:C13,"",D2:D13)

HTH –Sam

Reply | Quote

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 ( formulas with database functions.

For (a), see also:

http://www.mrexcel.com/wwwboard/messages/8961.html%5B/url%5D

Aladin

Reply | Quote