Excel 2007 : Monthly sum of values

[dalaman]

Hello,

i have two columns, one with dates and another with values.

How can I sum values for every month ?
Example of output (values doesnt fit with values in attachement):

Month | Sum
01.08 | 456
02.08 | 356
03.08 | 378
04.08 | 184
.
.
.

Thankks a lot

[JBeaucaire]

Here's one way...

[dalaman]

thank you for quick and correct answer

its perfect

[dalaman]

What does "--" mean ?

[JBeaucaire]

In Excel, if you multiply TRUE by 1 it equals 1. Weird, but it's true. If you multiply FALSE by 1 it equals 0. But it's long and ugly to multiply the entire array with another formula. So the Excel "gurus" discovered a way to do it with only two characters added.

If you put a "minus" in front of any formula, it effectively multiplies that formula by -1 without you having to add anything else. Try it. Put 15 in a cell, perhaps A1, and in B1 put the formula =A1. You get 15. But if you put =-A1 it becomes -15.

By the same token, if you put TRUE in A1, and =A1 in B1 you get TRUE, but if you put =-A1 you get -1. Aha! So with a single digit, we turned TRUE into a number, not "formula" needed...

So guess what happens when we put TWO minuses in front? Put TRUE in A1 and in B1 put =--A1 ...you get 1.

Now, let's look at the arrays inside a SUMPRODUCT() formula...
(A1:A5=B10) {FALSE,FALSE,TRUE,FALSE,TRUE}

But put the two minuses in front (called a unary coercion, by the way):
--(A1:A5=B10) {0,0,1,0,1}

That's useful because we're then going to do math against the other arrays. So one array tests all the cells for month values and you get an array back of the ones that DO and DON'T match the month test. The next array tests the years, another array, and finally you get an array of just the values to be added, no test in that formula. So you end up with 3 arrays:

{0,0,1,0,1} only two matched the the month
{1,1,1,1,1} they all matched the year
{5,5,5,5,5} the values to add

Now, Excel multiplies the values in the same position in each array. They are:
0x1x5 = 0
0x1x5 = 0
1x1x5 = 5
0x1x5 = 0
1x1x5 = 5

So the -- allows Excel to use TRUE/FALSE answers in the creation of a long array of multiplication formulas.

Bob Philips explains more here:
http://www.xldynamic.com/source/xld....CT.html#format

[dalaman]

Wow, what a beautiful solution, i've already understood it. Thank you very much for excelent explanation

I think, this feature will be useful for me

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)