SUMIF OR SUM IF

[Harvey Waxman]

Thanks to several of you, I successfully us this formula to determine a
total of the values in the range in G9817:G10713

{=SUM(IF(($G$9817:$G$10713<M10),$G$9817:$G$10713))}

I'd like to enhance this so that I don't have to first sort H to find
the relevant range.

I sort column I so that I can identify the range of the values in H that
have the word "sale" in I. There must be an additional IF statement I
can add to the formula so I can apply it to the entire column G1:G11000.

"Give the total of all the values in G that have the word "sale" in I
and are less than M10"

I hope this is clear and thanks again

 

[Harvey Waxman]

Thanks to several of you, I successfully us this formula to determine a
total of the values in the range in G9817:G10713

{=SUM(IF(($G$9817:$G$10713<M10),$G$9817:$G$10713))}

I'd like to enhance this so that I don't have to first sort "I" to find
the relevant range.

I sort column I so that I can identify the range of the values in G that
have the word "sale" in "I". There must be an additional IF statement I
can add to the formula so I can apply it to the entire column G1:G11000.

"Give the total of all the values in G that have the word "sale" in "I"
and are less than M10"

Get the "H" out of there. Sorry.

 

[JE McGimpsey]

Thanks to several of you, I successfully us this formula to determine a
total of the values in the range in G9817:G10713

{=SUM(IF(($G$9817:$G$10713<M10),$G$9817:$G$10713))}

Easier (i.e., you don't need to array-enter):

=SUMIF($G$9817:$G$10713, "<" & M10)

I'd like to enhance this so that I don't have to first sort H to find
the relevant range.

I sort column I so that I can identify the range of the values in H that
have the word "sale" in I. There must be an additional IF statement I
can add to the formula so I can apply it to the entire column G1:G11000.

"Give the total of all the values in G that have the word "sale" in I
and are less than M10"

=SUMPRODUCT(--($G$1:$G$11000<M10),
--ISNUMBER(FIND("sale",$I$1:$I$11000)), $G$1:$G$11000)

use SEARCH() instead of FIND() if you need the match to be
case-insensitive.

 

[Harvey Waxman]

=SUMPRODUCT(--($G$1:$G$11000<M11),--ISNUMBER(FIND("sale",$I$1:$I$11000)), $G$1:$G$11000)

use SEARCH() instead of FIND() if you need the match to be
case-insensitive.

Works perfectly. Thanks

While I was waiting I tried this, which worked:

=SUM(IF(($G$29:$G$1000<$M$11)*(I29:I1000="sale"),$G$29:$G$1000))

Will there be a problem with it?

Now one more thing, how can I add the requirement that the values in G
also have to fall in a range, eg. >=M11 and <M12.

I tried this but with no success:

=SUM(IF(($G$29:$G$1000<M12)*($G$29:$G$1000>=M11)*($I$29:$I$1000="sale"),
$G$29:$G$1000))

Thanks once again