Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:
  1. =SUMIFS(G2:K2,$G$1:$K$1,"="&
    Code:
    INDIRECT("M12")
    ,$G$1:$K$1,"<="&
    Code:
    INDIRECT("M15")
    )

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reference cell in one TAB from another using two criteria Jeff Excel Worksheet Functions 4 August 8th 07 10:27 AM
COUNTIF cell reference won't work Kenny Newbry Excel Worksheet Functions 4 June 26th 07 07:59 PM
Can I set the criteria in a sumif statement as a cell reference? Duncan Excel Worksheet Functions 3 July 27th 06 05:46 AM
IF Function Does Not Work With Cell Reference Gary Excel Worksheet Functions 4 June 25th 06 05:57 PM
SUMPRODUCT Criteria Via Cell Reference?? John V Excel Worksheet Functions 8 April 12th 06 07:55 PM


All times are GMT +1. The time now is 07:50 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"