sumif with cell value as criterion (2003)

Topic

New Reply

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?

Reply | Quote

Replies

Hi John

Try

=SUMIF(savings,”=”&B41)

Reply | Quote

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

Reply | Quote

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

=COUNTIF(savings,"<=B41")

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

=COUNTIF(savings,"<="&B41)

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.

Reply | Quote