summing based on a criteria but the criteria is not the format i want

[uimhirADo]

Hi there

hopefully the question is in the title: summing based on a criteria but the criteria is not the format i want
I have come up with a solution in Sheet1A but i don't want to add another column.
Please ask any question if the question is not clear.

See the attached.

1. How do I get the sum of ValueA for the month of June and put it in column G2
2. Assume that hourly data will be appended to this so in future I will want to get July values…etc
3. I am looking to do the same for ValueB & ValueC
4. I could add another column in column as I have done here and this works (Answer I want is in column J) see Sheet1A

Please Login or Register  to view this content.

Tks

[MarvinP]

Hi Tsk,

Does the Pivot Table answer work for you?

[uimhirADo]

Hi MarvinP

That is food for thought thanks.
But it's not exactly what I'm looking for.
The data in hourly format feeds a chart and is already in pivot chart format that I want to keep it in (an aside maybe I can do a pivot of an existing pivot??)
So basically I want to know how to sum based on a criteria that is not in the format that I want...
thanks

[Ace_XL]

How about..

=SUMPRODUCT(B$2:B$486,--(MONTH($A$2:$A$486)=6))

[kvsrinivasamurthy]

IN G1,H1,I1 put date 1/6/2013 and format as mmm.

IN G2

Please Login or Register  to view this content.

IN H2

Please Login or Register  to view this content.

In I2

Please Login or Register  to view this content.

[uimhirADo]

Tks Ace_XL & kvsrinivasamurthy they both work.