Excel Array Formula Help

d|b · 28 Jul 2010 at 13:15

I am using the Excel Conditional Sum Add In to generate the following formula:

{=SUM(IF(Costs!$A$2:$A$5000=2010,IF(Costs!$D$2:$D$5000="Executive Management",Costs!$F$2:$F$5000,0),0))}

What I am trying to do is have a cell that sums the monthly cost of all entries in my cost sheet labelled "Costs" which have the following characteristics: "2010" and "Executive Management".

This works fine when I use the wizard to generate the above formula. The problem arises when I copy this formula to another cell and then change one of the characteristics manual (e.g "2010" to "2011")..then the formula doesn't return anything.

Do I need to refresh the sheet or something. What am I doing wrong?

Meeko · 28 Jul 2010 at 13:22

When you are inputting the formula in another cell, make sure you press CTRL+SHIFT+ENTER (rather than just enter). This forces excel to recognise it as an array formula rather than a standard one.

Meeko · 28 Jul 2010 at 13:25

PS... Or you could use the SUMPRODUCT function as follows :

=SUMPRODUCT((Costs!$A$2:$A$5000=2010)*(Costs!$D$2:$D$5000="Executive Management")*(Costs!$F$2:$F$5000))

No need to input that as an array formula using CTRL+SHIFT+ENTER, just the formula above followed by ENTER.

Malakite · 28 Jul 2010 at 13:26

For a forumla like that i would normally use a sumproduct formula.

So the equivilant to your formula would be

=sumproduct((Costs!$A$2:$A$5000="2010")*(Costs!$D$2:$D$ 5000="Executive Management")*(Costs!$F$2:$F$5000))

I would also change your criteria e.g. ="2010" to reference a cell with the criteria in it.

So put 2010 in cell A1 then change that pasrt of the formula to (Costs!$A$2:$A$5000=A1)

This way you can just change the value in that cell to change your forumla accordingly.

edit: too slow

d|b · 28 Jul 2010 at 13:31

Right...thanks for the help. I love how geeky this forum is

Will give it a shot and report back.

d|b · 28 Jul 2010 at 13:34

Works like a treat. Thanks people!

Meeko · 28 Jul 2010 at 13:36

Malakite said:
For a forumla like that i would normally use a sumproduct formula.

So the equivilant to your formula would be

=sumproduct((Costs!$A$2:$A$5000="2010")*(Costs!$D$2:$D$ 5000="Executive Management")*(Costs!$F$2:$F$5000))

I would also change your criteria e.g. ="2010" to reference a cell with the criteria in it.

So put 2010 in cell A1 then change that pasrt of the formula to (Costs!$A$2:$A$5000=A1)

This way you can just change the value in that cell to change your forumla accordingly.

edit: too slow

Great minds think alike mate! Further to this, you could also do the same for the "Executive Management" part and put that in a fixed cell with a list of options (data validation) that users can select.