Excel monthly sums by date in single column? 2

[attrofy]

I have a spread sheet that keeps an on going running total of gallons of fuel pumped out of our pumps. These readings are not always read once per day. So in some months I may have 25 readins, other months 31, other months only 15. In any case, I have a "working sheet" that records the date, plus all the pertinent info (pump number, metered readings, etc).

How can I extract the monthly totals and get a month end tally, based solely on the number of the month? Column A has date, and is a date field. How can I extract only the data where the month equals 7? Understand I am not looking to filter the data, I am looking to calculate other columns based only in the Month of July. This will be an on going sheet that will have data added on a semi-daily basis, thus I can't have a "range" of 30 or 31 rows to tally.

Any suggestions? Hopefully this is clear enough to elicit the responses I am looking for. I have messed with the MONTH() and SUMIF() functions, but haven't found a workable combination that will tally all of the July entries.

Thanks in aadvance for any help.

 

[SkipVought]

attrofy,

The PivotTAble Wizard can do exactly what you want, AS LONG AS you have REAL DATES and EVERY row in the table has a DATE.

Once you have formatted a PivotTable, right click the Date Field and select Group & Show Detail/Group and then select MONTH & YEAR for grouping.

If you know what you are doing, it shoud take about 30 seconds to format this report.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[bluedragon2]

=SUMPRODUCT((MONTH(A1:A100)=1)*(B1:B100))

where col a is your date and col b is what you want to sum up.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

oh yeah, and a pivot table will do it too

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[attrofy]

I am running out the door, but I will work on these tomorrow. Will a pivot table allow me to show multiple months, as well as compare metered amounts to pumped amounts and fillups as well. I have never been able to grasp the Pivot Table - it never retuirns the data like I think it will. I will give this a whirl though. The SUMPRODUCT() may work - as there are multiple bits of data I need to pull from each entry.

Thanks for the fast replys - I will report back my findings tomorrow.

 

[KenWright]

You really must must must must must must must must look Pivot Tables for this. They will do this for you in a heartbeat, and if you are adding data to the sheet, then you can use a dynamic named range to capture it, which means you won't have to change any formulas at all. If it sounds comolicated it really isn't, and if you give us an example of your data, we can give you a few simple steps to set it up.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

DITTO, Ken! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[attrofy]

Ok,
The SUMPRODUCT() is giving me a #VALUE error. Not sure what is up with that....
=SUMPRODUCT((MONTH(A1:A21)=6)*(E1:E21)) See below for data.
EDIT: This worked =SUMPRODUCT((MONTH(A2:A21)=MONTH(A2))*(E2:E21))
Those danged header rows

For the Obi-Wons of Pivot tables - teach me....

My Spreadsheet is not fixed - this is just what I have to work with, but as I am revising, I have full reign to change any criteria as needed. With that in mind....

To start with, we are trying to calculate the overage/underage of three fuel pumps (tied to the same supply tank) based on what the pumped totals of all three pumps read verses what the telemetry meters read. Combine that with the running totals of gallons pumped per week/month, and the gallons purchased (qty and dollars).

As it stands, I have separated out the pumped data from the metered data, and plan on including the "purchased" data in with the metered data, as that will be the "control group" as those numbers are fixed (based on accuracy of the metering equipment). And, as it stands, what we are trying to find out is the "error" factor through spillage, evaporation, human error, etc.

Metered Total = B+C+D
Metered Difference =IF(AND(E2>E1,E2<>0,E1<>0),E2-E1,0)
Monthly Total =IF((MONTH(A2)=MONTH(A3)),F2+F3, H1 )

Sorry for the confusion of terms, as it stands on this page, "Metered" means "pumped" and that which is "metered" through telemetry counters is called "inventoried" (on another page) - this will be changed in the revision.
Also, note that there are gaps in the "consecutive" days (Jun 11-14) - so fixed ranges probably will not work. Which brings up an interesting question of how to assign dynamic ranges? But one thing at a time.

Thanks in advance for the help...teach me the ways of the Pivot Table....

 

[attrofy]

Alright, since I am not getting a response on the Pivot Table requests, let me confuse the thread. I am trying to setup a dynamic range as per the FAQ above - great FAQ BTW. I read through all of the formulas, and understand completely what is supposed to happen, but for some reason isn't:

=OFFSET(INDIRECT('METERED TOTALS'!$A$1),0,0,COUNTA('METERED TOTALS'!$A:$E),COUNTA('METERED TOTALS'!$2:$65536))

This seems to be creating a Named Range (NEW_DATA) - but it isn't adding it to the drop down list. Moreover, any calls to reference it seem to be erroring out in Circular reference errors - even on a different page. ANy thoughts what is wrong? BTW, this eferences the same A:E columns in the above example.

 

[SkipVought]

Hi,

Incorrect offset formula
[tt]
=OFFSET(INDIRECT('METERED TOTALS'!$A$1),0,0,COUNTA('METERED TOTALS'!$A:$A),COUNTA('METERED TOTALS'!$1:$1))
[/tt]
COUNTA('METERED TOTALS'!$A:$A) counts the number of rows in your table INCLUDING the heading row...
COUNTA('METERED TOTALS'!$1:$1) counts the number of columns in your table



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[KenWright]

LOL - Give us a chance Don't forget, none of us actually work for the site, or anything like that, we simply pop on in our spare time and do what we can to help out. This means that when it's like today, really sunny and a day off, I'm not going to be spending too much time indoors if I can help it.

Now as far as actually using the Pivot table, what I'll walk you through initially is how to recreate what that SUMPRODUCT formula gave you, but with a lot more power and flexibility:-

First though, lets be sure that the data is 'clean' for what I want to do. I need a date in every single cell in your Date field in Col A for your entire block of data. I can live with blanks elsewhere, though these can cause other niggles, and we'll get to those, but for now just make sure there are no blanks in Col A.

Select all your data, header rows included, do Data / Pivot Table anad Chart Report / Hit Next / Next / Finish. Now drag Date to the left of the table that just appeared into where it says ROW FIELDS, and then drag 'Metered Total' into the middle where it says DATA.

Now RIGHT-CLICK on any of the dates and select Group & Show Detail / Group. You will see that months is already selected, so scroll down and also select Years and then hit OK. You should now see a filtered list showing you totals for each month of your data. That's just the start though, so what other data you need to get out of here?

By the way, if what you see doesn't appear to be a SUM of the Metered data, then you may well have a blank in your column of metered data. This is what I was referring to before, and if this is the case then what it has done is give you a COUNT of the records instead of a SUM. It's easy to fix though, so you just right-click on any of those values in the Pivot table, choose Field settings and then where it says 'Summarise by' on the left, change it from COUNT to SUM and hit OK.

If you suddenly decide you would rather see the months across the top of the spreadsheet, then just click and drag the grey header from where it is at the top of the ROW fields and drag it to the left of the COLUMN fields. You can do this with any of the fields and the report will change before your eyes

Regards
Ken..............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[attrofy]

Ken,

LOL, not trying to imply impatience - but had to laugh....we were having the exact opposite weather, and it was too gloomy to do anything else but post on here. Thanks for the info, will give it a whirl Monday morning. BTW, no blanks, that is what I was trying to resolve from the outset, as it was causing complications even with regular formulas.

Skip,

I tried that - Copied and pasted as you had listed in the tutorial. That wasn't working either, maybe I had typed the page name wrong - will try again. My confusion has been clarified. Too much Excel in so little day....

 

[SkipVought]

That offset formula is for the entire table. the NAME defining that range would be used in the SOURCE DATA REFERENCE for, for instance, a Pivottable.

How are you using the Range Name?



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[attrofy]

Skip,

I managed to make it work from home (however, I never do get the reference in the drop down box), and I found the INDIRECT() method has little value for my purposes (at the moment) as I have no need to reference only one cell for the value of the range. In case anyone is following along, in order to make the range dynamic to only include a limited number of Columns (as in my example I wanted A:E) this works:

=OFFSET((Sheet1!$A$1),0,0,COUNTA(Sheet1!A:A),5)

I guess this pretty well just defines the Offset() method, but by combining the CountA() for the height or width it makes the specified range dynamic. So, putting in a fixed count for either of those values limits the range to only include certain data. As in my case, everything after E is only "report analysis" of the hard data contained in A:E.

Anyway, a joyous moment of epiphany....

 

[SkipVought]

The reason for the INDIRECT is to anchor the reference, to prevent and INSERT row from changing the definition.

In your case it should make no difference.

You are applying the concept properly for a fixed 5 columns.

Good luck!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[attrofy]

ahhh, that makes sense, and an inserted Column would cause an issue with the way I am attempting to do this. More later. Thanks again for everyones help.

 

[KenWright]

Just as an addendum, the reason I am hard over on having a date in every cell is because you cannot use the GROUP option I referered to if you have any blanks in the list of dates. You will simply get an error message that says something like 'Cannot group that selection'.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

DITTO to Ken! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244