sumifs will not work with a cell reference as criteria

[Guest]

Where cells M12 and M15 contain the number 39315 and 39317, respectively.
The formula yields no results when M12 and M15 are in the formula. However,
when I substitute the actual number in the formula, the proper result is
produced.

=SUMIFS(G2:K2,$G$1:$K$1,">=M12",$G$1:$K$1,"<=M15") does not work
=SUMIFS(G2:K2,$G$1:$K$1,">=39315",$G$1:$K$1,"<=39317") works

 

[Guest]

Try:

=SUMIFS(G2:K2,$G$1:$K$1,">=" &M12,$G$1:$K$1,"<=" &M15)

 

[Peo Sjoblom]

Append the cell reference with the ampersand otherwise excel sees it as a
text value and looks for the text string


">=M12"

will look for the string "M12" not the contents


">="&M12

will look for the contents of cell M12

 

[Guest]

Thanks very much. Very helpful.