Sumif Range

[carianne]

I have a sumif formula that references a range that I want to remain static. I update the data daily and sometimes the range varies based on how many patients are in our beds. The max it will ever be is 314. I used the following formula:
{=SUM(IF('Census By Service'!$F$2:$F$314="NEU",'Census By Service'!$M$2:$M$314,0))}

However, everyday the formula readjusts the range based on the data I enter, so I have to update the formula range daily. I want it to always look through the range max, but even adding the $ sign before the range does not seem to accomplish this. Any suggestions?

 

[pgc01]

Hi carianne

You can use INDIRECT.

=SUM(IF(INDIRECT("'Census By Service'!$F$2:$F$314")="NEU",INDIRECT("'Census By Service'!$M$2:$M$314"),0))

This formula will always look at the same ranges even if you add or delete lines.

Hope this helps
PGC

 

[sailepaty]

One more option,

=SUMIF('Census By Service'!$F$2:INDEX('Census By Service'!$F:$F,314),"NEU",'Census By Service'!$M$2:INDEX('Census By Service'!$M:$M,314))

Regards

 

[carianne]

Thanks so much!