Why does sumifs not recognise a cell reference value as a valid criteria

[johnlink]

Hi and greetings,

I am trying to use last years sales data to compare to this year for certain sales categories.
However I need to limit the sum range of the historical data to the current week number eg week 20 as this years figures only go up to the current week eg week 20.

I have a cell into which I type in the number 20 and I want the sumifs function to return last years sales totals up to week 20 and exclude any weeks over that count number.

My formula

=SUMIFS(B5:F5,B3:F3,"<=D1")

B5:F5 is the range of sales values
B3:F3 is the range of week values (criteria)
D1 is the cell containing a specific week number for which I need sales summed up to and including this week.

Problem is using this formula gives a result of zero!
If I manually replace the D1 reference with a value eg 18 then the formula works fine.
If I remove the quote marks around the reference I get a formula error message.

Please help a very confused (and pissed) Australian!!!!

 

[admiral100]

Hi,

Try this : =SUMIFS(B5:F5,B3:F3,"<="&D1)

 

[FormR]

Hi, welcome to the board!

It should look something like this:

=SUMIFS(B5:F5,B3:F3,"<="&D1)

 

[johnlink]

Hi,

Try this : =SUMIFS(B5:F5,B3:F3,"<="&D1)

Great thanks a lot for that it works a treat!!

Not quite sure why the syntax has to be in that format?

Anyway I can now get on with my excel appliation for comparing this year to date Vs last year up to same to date week #.

 

[johnlink]

Hi, welcome to the board!

It should look something like this:

=SUMIFS(B5:F5,B3:F3,"<="&D1)

Thanks also to FormR your solution works too!!

 

[FormR]

Glad we could help

Not quite sure why the syntax has to be in that format?

The formula is expected the criteria string to be something like "<=10" for example - the & character concatenates the "<=" part with whatever value is in the cell D1 to create said string.

 

[Rick Rothstein]

Not quite sure why the syntax has to be in that format?

This was the formula you wrote...

=SUMIFS(B5:F5,B3:F3,"<=D1")

Anything inside quote marks is nothing more than a collection of characters with no other meaning than that, so the D1 in your formula is nothing more than two characters, a "D" followed by a "1". If you think about it, you will come to realize that the D1 inside quote marks can never be a cell reference because if it could, how would you ever write the text string "<=D1" (simply those 4 characters as written) in Excel if every time it saw D1, it replaced it with the contents of cell D1?

 

[johnlink]

This was the formula you wrote...

=SUMIFS(B5:F5,B3:F3,"<=D1")

Anything inside quote marks is nothing more than a collection of characters with no other meaning than that, so the D1 in your formula is nothing more than two characters, a "D" followed by a "1". If you think about it, you will come to realize that the D1 inside quote marks can never be a cell reference because if it could, how would you ever write the text string "<=D1" (simply those 4 characters as written) in Excel if every time it saw D1, it replaced it with the contents of cell D1?

OK thanks for that I am sure I will need that again so will add it to my RAM (left hemisphere).