Numbers - SUMIF and YEAR Function Together - can't make it work...help an idiot!

If Barry is right and those numbers are cumulative readings, then you can still do this easily with Smart Categories.

Just duplicate the column with the readings, and add another column to subtract the minimum from the maximum for that years. Numbers takes care of all the grouping by year and the minimums and maximums. You need only one simple formula.

The formula in the body cells in column D:

$C 2017 (Maximum)−$B 2017 (Minimum)

Enter that by clicking the appropriate cell in the maximum Group "heading", typing minus, and then clicking the appropriate cell in the minimum Group "heading".

SG

Hi Rem',

"I want to sum all the gas meter readings from a specified year."

I think you need to rethink your question.

Ignoring the method to use, what is the actual question you are trying to answer?

I suspect it is "How much gas did I use in the year xxxx?"

The SUM of a subset of the numbers (or 'some' of the numbers) in column B is unlikely to provide an answer to that question.

Is your gas meter reset to zero after each reading is taken (like the gasoline/petrol pump at your service station/garage)?

Or does each reading tell the amount of gas used since the time the meter (reading 0000) was originally installed (like the odometer in your car does with distance,, starting from the time the car was built)?

If your gas is delivered by truck, and pumped into a tank on your premises, the meter, located on the truck, may operate in the same manner as the one at the service station, measuring the amount of gas delivered to you.

Each reading is separate, and the total amount of gas delivered to you will be the sum of the amounts delivered on all visits of the truck to your storage tank.

If your gas is delivered by pipe, the meter, installed where the pipe enters your home, is more likely to operate like the car odometer, measuring the amount of gas that has passed through it since the 'beginning'.

Each reading is cumulative, and includes ALL of the gas that has been delivered to you since the meter was installed.

The list of numbers in the Gas Number column of your table, where each number in the list is larger than the number in the previous row, would suggest the readings, like those of an odometer, are cumulative, and the meter is NOT reset after each reading.

If that's the case, the SUM of those numbers doesn't carry a lot of meaning.

Suppose your house fit the first example.

The tank was installed January 1, 2018, and the empty tank was filled the same day, with 10 units of gas. You were handed the invoice for 10 units.

One month later, the truck returned and filled the tank again. The invoice was printed from the meter and you were billed for 8 units.

On March 1, and again on April 1, he truck made two more visits. The March 1 invoice invoice was for 9 units, the April one for 7 units,

(The sum of the numbers is 34)

How much gas did you use in the first quarter of the year?

Now suppose your next door neighbour, whose house is identical to yours, but has a different gas supplier, who delivers his gas by pipe, and sends a meter reaader to read the meter each month. Like yours, his gas service began January 1, when his newly installed meter read 0. By February 1, when the meter reader, in a (smaller) truck came to read the meter, the reading was 8 units, on March 1 it was 17units, and on April 1 it was 24 units.

(The SUM of the numbers is 49)

How much gas did your neighbour use in the first quarter of the year?

Which example is most like your actual situation?

Regards,

Barry

suggest creating an extra column to make this easier.

I added column B.

select cell B2 and type (or copt and paste from here) the formula:

=YEAR(A2)

shorthand is:

B2=YEAR(A2)

to fill down, select cell Ba, copy

select cells B2 thru the end of column B, paste

Now sum the result:

=SUMIF(B, 2018, C)

HI SG,

For a cumulative metering situation, this MAX-MIN within a category solution comes closer to an answer to "how much gas did I use in a year. But it will alway result in an under-estimate, as it does not account for the 1111 units used between the last reading in 201 and the first in 2018, the 665 units used between the last reading in 2018 and the first in 2019 (or for any amount used after October 10, 2019).

This could be estimated by assigning an interpolated value for Dec 31 AND Jan 1 at each year end/beginning, or by the customer taking an actual reading on one of those two days and entering it for both days. A straight line interpolation would likely be accurate enough if the weather was reasonably consistent during the time between the last reading in the earlier year and the first in the following year, but a heat wave or a cold spell on either side of New Year's would skew the results in a community where that is the heating season.

Regards,

Barry

Barry wrote:

For a cumulative metering situation, this MAX-MIN within a category solution comes closer to an answer to "how much gas did I use in a year. But it will alway result in an under-estimate

A yes. If those are indeed cumulative readings then there is an easier solution using Smart Categories. Simply subtract the maximum of the previous year from the maximum of the current year.

Again, entering the formula, which must be done in the "body" cells of each year, is just point-and-click.

SG

Hi SG,

Closer, and assuming that the meter reading dates listed are random, and the actual dates, (particularly the last-of-year reading) are more regular (e.g. within a few days of the 'anniversary'), a MAX - MAX calculation will give a useable approximation.

Regards,

Barry

No need for an extra column, or even a formula! Just Add Category and you're done in a few seconds with just a few clicks.

SG