conditional sum - copyng formula

O

offcott

I have conditionla sum formula in one cell which I wish to :
a) copy across to another sheet in same workboo
b) copy into other cells on same sheet but incremant the values in the
formula, I wsih to change complete to new, butwhen I copy to another cell I
and change the word I get error, probably because I cannot change the
destination cell.

Cheers

=SUM(IF($C$2:$C$701="Capability",IF($H$2:$H$701="Complete",IF($I$2:$I$701="Administrator",$B$2:$B$701,0),0),0))
 
J

Joel

If you paste the formula into the cell then you have the permission to
changge the cell. Editing formulas can cause problems. first, never use the
backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete character
one at a time. Then enter new characters. Then click on worksheet to end the
editing of the formula.
 
S

Sandy Mann

What version of XL are you using Joel?
changge the cell. Editing formulas can cause problems. first, never use
the
backspace key (it changes the address in the formula)
Click to expand...

The Backspace works just fine for me in XL 97. You are not thinking about
the arrow keys in Conditional Formatting where they do enter new cell
references are you? Whereas:
backspace key (it changes the address in the formula). Click on the cell
then do the editing in the F(x) box at the top of the worksheet. Click on
the first character of Complete the press the delete key to delete
character
one at a time. Then enter new characters. Then click on worksheet to end
the
editing of the formula.
Click to expand...

In the fx dialog box clicking on the worksheet *will* enter the cell
reference for that cell and does not end the editing of the formula.

--
Puzzled,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
O

offcott

Sorry I am using Excel2002, Managed to copy and edit, but what is the { used
for that I have to put in at the beginnig of the formula, after I use thisit
works, but why?

Eric
 
J

Joel

The bracket specifies an array. Usually I use Sumproduct which doesn't
require the bracket in 2003.
 
S

Sandy Mann

Sorry if I have caused confusion Eric, I was asking Joel what version of XL
he was using because his instructions do not work for me in XL97.

The { } around your formula,which should be put there by XL when you enter
the formula by pressing and holding the Ctrl and Shift keys whil you press
Enter tell Xl that it is an array formula and to work on each cell in the
ranges in the formula otherwise it will just use the first cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
G

Gord Dibben

Sandy

When working in the formula is: dialog in CF, hit F2 key then you can use arrow
keys.

Same for the refers to: dialog in Insert>Name>Define.


Gord
 
S

Sandy Mann

Thank you for that Gord - that has been a bugbear to me for ages.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top