# Thread: sumif with cell value as criterion (2003)

1. ## sumif with cell value as criterion (2003)

A formula like
=SUMIF(savings,"<=1000",savings)
works ok.
However, =SUMIF(savings,"<=B41",savings) doesn't.
Nor does =SUMIF(savings,"<=(value(b41))",savings)
What's the syntax to generate what I'm after, please?

2. ## Re: sumif with cell value as criterion (2003)

Hi John

Try

=SUMIF(savings,"="&B41)

3. ## Re: sumif with cell value as criterion (2003)

Yep, that does the trick, Jezza.
Should I have known this?
How?
With the equivalent COUNTIF formula, like this
=COUNTIF(savings,"<="&B41),
Excel puts the quotes and ampersand in itself, when the formula is constructed in the build-a-formula dialogue box.
Thanks again

4. ## Re: sumif with cell value as criterion (2003)

It's important to keep in mind that the second argument of SUMIF and COUNTIF - the condition - is a text string that is interpreted literally. If you use
<code>
=COUNTIF(savings,"<=B41")
</code>
Excel will compare the value if each cell in the savings range to the text string B41, and count it if the value is the same or before it in dictionary order. If you use
<code>
=COUNTIF(savings,"<="&B41)
</code>
Excel will first evaluate the expression "<="&B41. Say that B41 contains the value 37, then the expression evaluates to "<=37". So Excel will compare the value if each cell in the savings range to 37, and count it if the value is less than or equal to 37.

#### Posting Permissions

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