I use a spreadsheet to calculate fuel cost/economy and usage over the course of the year. Individual columns show date (in date format), fuel cost, mileage, etc. Then by simple division or multiplication I can calculate the cost per mile, economy per tankful (somewhat different depending on long or short haul trips).
My problem arises in the summation of the yearly costs. At the bottom of data for each vehicle I do a summation. The first column shows the date the vehicle was first used. The second column shows that date in number format (for now by necessity). The rest of the columns use a variation of the following formula.
=SUM IFS(E$1:E$149,$A$1:$A$149,">=42370",$A$1:$A$149,"<42736")
There is no space between the SUM and IFS. Was unable to post without the space.
Quite simply, I want to add the values in column 'E' if the values fall between 42370 and 42736. 42370 is 3/31/16 and 42736 is 1/1/17. When I write the formula this way, everything works. However, if I write the formula using B152 (the cell in which 42370 is located) and B153 (the corresponding cell for 42736), I get a an answer of 0.00 or some other null answer. What am I doing wrong? Over the course of time, for multiple vehicles, I have to rewrite every formula rather than just copying the formula down one row for the next year.
Thanks, in advance for any suggestions.
