How to create a dynamic range based on MONTH()

MaximumGravity · Jul 30, 2004

After a week of an excel blitzkreig, my mind has mushified. I am trying to find the easiest, most compact way to sum a 2nd column, based on the month value in the first column. I need this to break at every monthly change, and keep a running total for each month.

Here is an example:

GAS OO Modified.xls

A

B

C

D

E

1

Date

TANKINVENTORY

CALCULATEDCONSUMPTION

INVENTORIEDFILL

MTDInventoriedConsumed

2

6/1/2004

2108.0

90.0

0

3

6/2/2004

2018.0

106.0

4

6/3/2004

1912.0

117.0

5

6/4/2004

1795.0

947.0

6

6/5/2004

2742.0

45.0

7

6/6/2004

2697.0

72.0

8

6/7/2004

2625.0

175.0

INVENTORIED TOTALS

Cell E2 is the formula that I am trying to calculate. In short, I want to SUMIF the MONTH() = 6. For E3 SUMIF MONTH() = 7, etc. Since this will be an ongoing datasheet, I need the month to be variable - in otherwords, only SUMIF A2 does not equal A3.

I am sure a LOOKUP() function would work, but the theory I have is to define a DYNAMIC RANGE using the OFFSET(A2,,COUNTA()) method, but the range would then have to DYNAMICALY reference itself - once A:A changed months.

I hope this makes sense. if further examples are needed, let me know. Also, I am not married to the calculations in Rows C & D - this is the pre-existing data that I have to work with. In short, B is a metered total, read from a daily print out. C is just the calculated difference to determine the daily consumption, and D is taken from the invoice that is billed to us for Gallons purchased. I think there may be a more fluid approach to these calculations.

Aladin Akyurek · Jul 30, 2004

It sounds as if you want a total in rows where a month change occurs. Right?

Zack Barresse · Jul 30, 2004

Aladin Akyurek said:
It sounds as if you want a total in rows where a month change occurs. Right?

If this holds true, maybe something like this would be in order ...

=IF(DAY(A2)=1,SUMPRODUCT(--(MONTH($A$2:INDEX(A:A,MATCH(9.9999999E+307,$A:$A)))=MONTH(A2)),$C$2:INDEX($C:$C,MATCH(9.9999999E+307,$A:$A))),"")

Which can be entered in a cell (e.g. E2) and copied down. This will only show a calculation on the first day of the month (that row) and should calculate for the entire month. Problems may occur if you have other (erroneous) data in column A than dates. HTH

Krishnakumar · Jul 31, 2004

Try a Pivot Table and group the Date Field with Months

Kris

PaddyD · Jul 31, 2004

"Try a Pivot Table and group the Date Field with Months "

Picky, but if there's data from > 1 year, you'll need to group by year & month.

MaximumGravity · Aug 2, 2004

Aladin,
That is a correct assumption.

Firefytr,
Your formula worked, except for one small caveat I was trying to avoid - this only totals the row on a row by row basis. Meaning that my monthly totals only appear every 30-31 rows, and I was trying to avoid pasting this formula into every single cell for 365 days.

More importantly two questions for you:

1.) What does the "--" do? I noticed it yeilds a differnet result with those omitted. A single "-" makes the value negative, what does the double "--" indicate.

2.) What does thevalue 9.9999999e+307 mean, and how did you derive it?

PaddyD,
That does bring up an interesting dilemia. As for the Pivot Table response, since that is only a reporting tool, can I calculate data from the calculated data in the Pivot Table?

Thanks all for the continued help.

Zack Barresse · Aug 2, 2004

MaximumGravity said:
Firefytr,
Your formula worked, except for one small caveat I was trying to avoid - this only totals the row on a row by row basis. Meaning that my monthly totals only appear every 30-31 rows, and I was trying to avoid pasting this formula into every single cell for 365 days.

More importantly two questions for you:

1.) What does the "--" do? I noticed it yeilds a differnet result with those omitted. A single "-" makes the value negative, what does the double "--" indicate.

2.) What does thevalue 9.9999999e+307 mean, and how did you derive it?

The "--" is a double negation coercion that translates boolean (TRUE/FALSE) values into binary (1/0) values. This is great for creating a multi-conditional count or sum function. Coupled with the inherent array nature of the SUMPRODUCT function, it will pit each array against each other, multiplying the values against one another. If you notice each array (seperated by commas) has a condition (=someValueHere), so each condition will check the cells in that particular array and return it's boolean value against the condition, basically bringing back an array of TRUE,FALSE,TRUE,FALSE, or however the range is identified. Enclose this in "--()" and it will translate into a 1,0,1,0 type of array, which can be computed in numerical values, in essence telling you how many cells in a particular range meet said given criteria. Pitch 2 arrays together, e.g. 1,0,1,0 and 1,1,0,0, and you'll get a finished product (because they are multiplied together) like 1,0,0,0. This is because 1*1=1, 0*1=0, 1*0=0, 0*0=0. So your count is 1.

The value 9.99999999E+307 is a value in Excel that is the largest recognizable integer value in a cell (from what I understand). So basically coupled with a MATCH formula, it will find the last row in a specified column with an integer (numerical) value in it. You can do this for textual, but it changes to REPT("z",255), as it's transversley the largest textual value allowed in a cell (again, afaik). If you wanted to couple both, it's be something like this: MAX(MATCH(9.99999999E+307,$A:$A),MATCH(REPT("z",255),$A:$A))

I hope this helps as an explanation. Aladin has an excellent post in the Hall of Fame on this type of solution.

So what were you thinking about for a solution? Did you want these figures running down a column in successive cells by month? You'd probably need a sepereate column with the months (1st day of the month) in it and then run a similar formula next to that. Or you could *hardcode* it in your formula. Can you post a sample of what you want your data to look like when completed?

MaximumGravity · Aug 3, 2004

Thanks for the excellent repsonse - that makes a lot of sense - except for the "--" part - I was under the impresion that excel automatically assigned integers to boolean responses. Depending on the calulation being performed. I thought a "FALSE" was a textual response, but automatically caluclated as a "0". I guess this isn't the case - or at least not always the case. I can see where a "FALSE" condition would not allow a second part of a formula to be calculated, or cause a "halt". Instead of comparing/calulating an array value, it would error out, or return a "FALSE" for the entire array - instead of given the numeric value of the condition.

As for desired output, what I was looking for was to report the monthly totals in succession - E2=Month1 total, E3=Month2 Total, E4=Month3 Total - etc. I hope that clarifies what I am looking for.

Thanks again for the excellent responses.