SUMIFS not working when using cell references instead of dates

Unfortunately you need to format the dates when concatenating with >, <= etc.

=SUMIFS(E3:E27,B3:B27,">="&TEXT(B30, "dd/mm/yyyy"),B3:B27,"<="&TEXT(B31, "dd/mm/yyyy"))

You've got the Lotus transition formula evaluation turned on for the sheet. Turn it off (bottom of the Advanced section in Excel options) and your formula works fine.

Hi

It's a File Settings issue. Note that when you test the dates in column B with =ISNUMBER(B31) the function returns a 1 rather than the normal TRUE

Go to File _ Options _Advanced and scroll right to the bottomdown and un-check 'Transition Formula Evaluation' and also un-check 'Transition formula entry.

I've not encountered that odd behaviour before. Obviously Excel can't make the connection between a 1 and True in this context.
Is it an old workbook that predates Excel 2000 when =ISNUMBER() was introduced and presumably some change associated with evaluating numbers as True?

Well that's a new one to me.

The problem arises because that setting prevents you from concatenating text and numbers, so the criterion ">="&B30 actually evaluates to #VALUE! (and the same for the other one), and nothing matches those criteria as there aren't any #VALUE errors in the date column.

Thanks, Guys... I've used cell refs for dates in SUMIFS for... well, forever. I was sitting here in total bewilderment at its refusal to add up here.

Codenumpty... if you REALLY want to make youself feel old, you could refer to "last millenium" instead!!