Excel SUM

[ibrindley]

I currently have a formula:

=SUM(IF(G8:G400="X",IF(F8:F400="Y",1,0)))

This gives me a count of all rows with Column G = "X" and Column F = "Y".

It is working fine and I have a number of cells with this formula in them that are all OK until.......

I try to edit the formula:

When I click on the formula to edit, column G is higlighted with a blue boarder and upon entering "CTRL Enter" the cell displays #VALUE! even though I haven't changed anything.

I am clueless - can anyone help please ?

 

[Juan Pablo González]

You need to enter with Control SHIFT Enter, not Control Enter, or just Enter.

 

[Aladin Akyurek]

On 2002-10-10 11:43, ibrindley wrote:
I currently have a formula:

=SUM(IF(G8:G400="X",IF(F8:F400="Y",1,0)))

This gives me a count of all rows with Column G = "X" and Column F = "Y".

It is working fine and I have a number of cells with this formula in them that are all OK until.......

I try to edit the formula:

When I click on the formula to edit, column G is higlighted with a blue boarder and upon entering "CTRL Enter" the cell displays #VALUE! even though I haven't changed anything.

I am clueless - can anyone help please ?

That's an array-formula that needs to be entered with control+shift+enter, also after an edit.

By the way,

=SUMPRODUCT((G8:G400="X")*(F8:F400="Y"))

which is normally entered and does the same computation.