Excel 2007 : conditional autosum

[confusus]

I have some sort of monthy reports going on. i have 2 columns (years 2009,2010), 12 rows (months), and total row.. What i want is, i know its simple, but i cant get it done properly: IF value in row in year 2010 is greater than 0/number/something (it is monthly input), sum values in colums A (2009) that many times, so i can do a comparison in row total. 2009-2010 (X months) automatically.

I know I will get my answer soon, thank you very much.

[NBVC]

Probably:

=SUM(A2:INDEX(A2:A13,MATCH(9.999999E+307,B2:B13)))

where A2:A13 lists your 2009 data and B2:B13 your corresponding 2010 entries.

[confusus]

"9.999999E+307" wait, what?

[NBVC]

This function:

MATCH(9.999999E+307,B2:B13) finds last numeric entry in a range.

It looks for the biggest number excel understands (9.999999e+307) and if it cannot find it it returns the last found entry that is smaller than that number... which would always be your last numeric entry.

[confusus]

=sum(c4:index(c4:c15;match(9,999999e+307;d4:d15))) ->

30.298,93 20.090,37
31.431,94 24.567,92
31.777,92 31.572,06
37.880,04 0,00
31.991,03 0,00
35.867,35 0,00
30.164,54 0,00
29.225,96 0,00
28.643,66 0,00
32.223,67 0,00
27.116,14 0,00
27.655,40 0,00
374.276,59 76.230,35

still sums all values in column C... :/

[NBVC]

It seems you already have 0's entered in the column... I assumed they were blank until filled with other than 0.

=SUM(C4:INDEX(C4:C15,MATCH(2,1/(D4:D15<>0))))

confirmed with CTRL+SHIFT+ENTER not just ENTER.

[confusus]

ok, it works like it should work.
but I dont quite understand its background, why would you divide 1 with (array<>0).
this being my first thread here, would you care for some in detail explanation?

Thanks, see you around!

[NBVC]

Try using the Formula Audit tool, available from the Tool menu... and evaluate the formula. You will see, in steps, how the formula works.

Basically the MATCH(2,1/(D4:D15<>0) part works like this.

Firstly, after confirmed with CTRL+SHIFT+ENTER, the function evaluates each cell in the D4:D15 range and checks to see if they are equal to or not equal to 0.... in this case, it will return an array of TRUEs and FALSEs where TRUE if cell is not equal to 0 and FALSE if it is equal to 0.

When an arithmetic function is applied to TRUE FALSE elements, they are automatically coerced to 1's and 0's respectively... so now we are dividing 1 by each TRUE/FALSE (or 1/0) and getting results of 1 when 1 is divided by TRUE(1) and #DIV/0 error for all others (i.e. 1 divided by 0).

Now the MATCH looks for a 2 amongst that range, and like the 9.999999e+307 formula I showed before, it looks for the last number in that range that is less than or equal to 2... that would be the last 1 in the range... then it matches that up with the corresponding item in the Indexed range of C4:C15.

Notice that the MATCH() function does not have the 3rd optional argument of 0... which means it does not look necessarily for an exact match, but the last item that is smaller than or equal to the lookup value.

Hope that helps.

[confusus]

It SURE does. Thank you, things are clear now.

[NBVC]

Great, now you can change your name to "Comprehendus"