Total production of energy in a specific months

**S_T996** · 07-08-2013, 11:31 PM

Hi,
I could use a little help with the following:

An Excel sheet with 2 tabs:

Tab #1 is named PRODUCTION_DATA and contains 3 columns of data
B contains 30 dates mixed between 2 calendar months
C contains the 30 dates in B configured to mmm to only show jun or jul (Made for the calculation only)
D contains an energy production output for each of the 30 days in MWh

Tab #2 named PRODUCTION contains a formula that I hope can calculate the total production of energy in a specific months, and in this case it would be July as specified cell C4 where I have a =TODAY() formatted to mmm, I have tried:

{=SUM(IF(PRODUCTION_DATA!$C$4:$C$34=C4;PRODUCTION_DATA!$D$4:$D$34))}

But there are clearly something wrong since the above formula do not recognize any production.

The sheet is attached, please help
Thanks

**MarvinP** · 07-08-2013, 11:43 PM

Hi,

This looks like a job for Pivot tables. See the attached where I did a simple pivot table and then grouped the rows by month.

**FDibbins** · 07-08-2013, 11:46 PM

lol sorry Marvin, but that sounded like the start of...this is a job for........suuuuuper piiiiiiiiivot!!!!!!!

**MarvinP** · 07-09-2013, 12:06 AM

I totally agree. Lots of the problems here are simple if the OP knows about X or Y or Z. Many times they simply need to sort or add a helper column. I/we see too many of these problems where a SumProduct would work but Pivots are just too easy to not visit....

I can't count the number of times I've done Pivot Tables on OP tables that would take an impossible formula to do otherwise. I'd use PowerPivots but the term has already been taken by Microsoft. So "this sounds like a job for Super Man" is as close as I can get.

Now - will the OP really see that Pivots do give them an answer and learn about them? That is the real question.

**FDibbins** · 07-09-2013, 12:11 AM

I must admit that I dont use them as often as I should, I really need to keep them closer to the top of my tool box

**S_T996** · 07-09-2013, 01:02 AM

Originally Posted by MarvinP

Hi,

This looks like a job for Pivot tables. See the attached where I did a simple pivot table and then grouped the rows by month.

Thanks MarvinP
That helps but are there any way of automatically updating the content of a pivot table? I'm automatically collecting data from other sheets so the data will shift 1 day every day.
Thanks again

**S_T996** · 07-09-2013, 02:56 AM

Originally Posted by S_T996

Thanks MarvinP
That helps but are there any way of automatically updating the content of a pivot table? I'm automatically collecting data from other sheets so the data will shift 1 day every day.
Thanks again

I just found both the manuel and the auto Refresh function in Pivot so that will fix my problem
Thanks again
MarvinP