Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.misc




sumifs will not work with a cell reference as criteria
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,"<=393 17") works 
#2




Answer: sumifs will not work with a cell reference as criteria
It sounds like the issue is with using cell references as criteria in the SUMIFS formula. One solution could be to use the INDIRECT function to convert the cell reference into a cell address that can be used in the formula.
Here's an example of how you could modify the formula using the INDIRECT function:
This formula should work as long as the values in cells M12 and M15 are valid cell references. The INDIRECT function will convert the cell references into cell addresses that can be used in the SUMIFS formula.
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.misc




sumifs will not work with a cell reference as criteria
Try:
=SUMIFS(G2:K2,$G$1:$K$1,"=" &M12,$G$1:$K$1,"<=" &M15) "BillGr" wrote: 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,"<=393 17") works 
#4
Posted to microsoft.public.excel.misc




sumifs will not work with a cell reference as criteria
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  Regards, Peo Sjoblom "BillGr" wrote in message ... 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,"<=393 17") works 
#5
Posted to microsoft.public.excel.misc




sumifs will not work with a cell reference as criteria
Thanks very much. Very helpful.
"BillGr" wrote: 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,"<=393 17") works 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Reference cell in one TAB from another using two criteria  Excel Worksheet Functions  
COUNTIF cell reference won't work  Excel Worksheet Functions  
Can I set the criteria in a sumif statement as a cell reference?  Excel Worksheet Functions  
IF Function Does Not Work With Cell Reference  Excel Worksheet Functions  
SUMPRODUCT Criteria Via Cell Reference??  Excel Worksheet Functions 