# Thread: Sumif With 2 Conditions (2000)

1. ## Sumif With 2 Conditions (2000)

HELP!
I would like to sum a range of cells in, say, Column A if 2 conditions are met: first, if the cell contents of each respective cell in Column B is a certain letter, say an "o", andsecond, if the date in each respective cell in Column C is, say, prior to July 1, 2003.
Any experts out there?
Thanks,
Jeff

2. ## Re: Sumif With 2 Conditions (2000)

Something like this array should workconfirm with ctrl-shift-enter)

<pre>=SUM(IF((B1:B100="o")*(C1:C100<DATE(2003,7,1) ),A1:A100))</pre>

change the range as appropriate.

Note: you can replace the SUM with any of the stat functions, min, max, average, count, var, stddev, etc if you want other info.

For more on array formulas see chip pearson's site:
http://www.cpearson.com/excel/array.htm
Steve

3. ## Re: Sumif With 2 Conditions (2000)

If your values are in A1:C20, then the following formula should do what you asked:

<pre>=SUM(IF((NOT(ISERROR(FIND("o",B1:B20))))*(C1: C20<DATE(2003,7,1)),A1:A20))
</pre>

This is an array formula which means you must press Ctrl+Shift+Enter to enter the formula in the cell.

4. ## Re: Sumif With 2 Conditions (2000)

This is good if the first criteria was to CONTAIN an "o", but I read his request:
<hr>if the cell contents of each respective cell in Column B is a certain letter, say an "o"<hr>
to mean that the cells had to ="o" NOT just contain an "o".

Steve

5. ## Re: Sumif With 2 Conditions (2000)

I read it to mean that the cell contained the letter o somewhere in a string. I think it could mean either.

#### Posting Permissions

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