Currently I am setting up a sheet and I am using the conditional sum wizard
for formulas. Once a formula is created the sytem won't let me copy or change
a formula. If I try to copy (Copy, paste special) a formula to another cell,
the formula won't work anymore. If I create a formula with the wizard and
afterwards change one of the parameters, the formula does not work anymore.

Can anyone give me a hint on how I can solve these issues? Thanks.

Post your formula here, and we might be able to help.

Often this wizard will set up the formula as an array formula (it will
have curly braces { } around it when viewed in the formula bar). If
you amend this formula you need to commit it using CTRL-SHIFT-ENTER
(CSE) rather than the usual ENTER.

Hope this helps.

Pete

On Jan 6, 9:59*am, MarcoKoenders
wrote:
Currently I am setting up a sheet and I am using the conditional sum wizard
for formulas. Once a formula is created the sytem won't let me copy or change
a formula. If I try to copy (Copy, paste special) a formula to another cell,
the formula won't work anymore. If I create a formula with the wizard and
afterwards change one of the parameters, the formula does not work anymore.

Can anyone give me a hint on how I can solve these issues? Thanks.

Hi Pete,

this is one of the formulas I am using.

=SUM(IF('Data Export'!$D$2:$D$3500="AGH",IF('Data
Export'!$O$2:$O$3500="A",'Data Export'!$J$2:$J$3500,0),0))

Thanks.




"Pete_UK" wrote:

Post your formula here, and we might be able to help.

Often this wizard will set up the formula as an array formula (it will
have curly braces { } around it when viewed in the formula bar). If
you amend this formula you need to commit it using CTRL-SHIFT-ENTER
(CSE) rather than the usual ENTER.

Hope this helps.

Pete

On Jan 6, 9:59 am, MarcoKoenders
wrote:
Currently I am setting up a sheet and I am using the conditional sum wizard
for formulas. Once a formula is created the sytem won't let me copy or change
a formula. If I try to copy (Copy, paste special) a formula to another cell,
the formula won't work anymore. If I create a formula with the wizard and
afterwards change one of the parameters, the formula does not work anymore.

Can anyone give me a hint on how I can solve these issues? Thanks.

Hi Pete,

Your suggestion has worked out fine.

Thanks a lot.

Regards, Martin



"Pete_UK" wrote:

Post your formula here, and we might be able to help.

Often this wizard will set up the formula as an array formula (it will
have curly braces { } around it when viewed in the formula bar). If
you amend this formula you need to commit it using CTRL-SHIFT-ENTER
(CSE) rather than the usual ENTER.

Hope this helps.

Pete

On Jan 6, 9:59 am, MarcoKoenders
wrote:
Currently I am setting up a sheet and I am using the conditional sum wizard
for formulas. Once a formula is created the sytem won't let me copy or change
a formula. If I try to copy (Copy, paste special) a formula to another cell,
the formula won't work anymore. If I create a formula with the wizard and
afterwards change one of the parameters, the formula does not work anymore.

Can anyone give me a hint on how I can solve these issues? Thanks.

Another (slightly shorter) way of writing this is:

=SUM(IF(('Data Export'!$D$2:$D$3500="AGH")*('Data Export'!$O$2:$O
$3500="A"),'Data Export'!$J$2:$J$3500,0))

and you would need to commit it with CSE, as advised.

Another way is like this:

=SUMPRODUCT(('Data Export'!$D$2:$D$3500="AGH")*('Data Export'!$O$2:$O
$3500="A"),'Data Export'!$J$2:$J$3500)

and this does not need to be entered with CSE.

Hope this helps.

Pete

On Jan 6, 11:59*am, Martin G
wrote:
Hi Pete,

this is one of the formulas I am using.

=SUM(IF('Data Export'!$D$2:$D$3500="AGH",IF('Data
Export'!$O$2:$O$3500="A",'Data Export'!$J$2:$J$3500,0),0))

Thanks.



"Pete_UK" wrote:
Post your formula here, and we might be able to help.

Often this wizard will set up the formula as an array formula (it will
have curly braces { } around it when viewed in the formula bar). If
you amend this formula you need to commit it using CTRL-SHIFT-ENTER
(CSE) rather than the usual ENTER.

Hope this helps.

Pete

On Jan 6, 9:59 am, MarcoKoenders
wrote:
Currently I am setting up a sheet and I am using the conditional sum wizard
for formulas. Once a formula is created the sytem won't let me copy or change
a formula. If I try to copy (Copy, paste special) a formula to another cell,
the formula won't work anymore. If I create a formula with the wizard and
afterwards change one of the parameters, the formula does not work anymore.

Can anyone give me a hint on how I can solve these issues? Thanks.- Hide quoted text -

- Show quoted text -

You're welcome, Martin - thanks for feeding back.

Pete

On Jan 6, 12:21*pm, Martin G
wrote:
Hi Pete,

Your suggestion has worked out fine.

Thanks a lot.

Regards, Martin

Hi,

One non-array way to write this is

=SUMPRODUCT(--('Data Export'!$D$2:$D$3500="AGH"),--('Data
Export'!$O$2:$O$3500="A"),'Data Export'!$J$2:$J$3500,0)

This would be the standard answer to how to write this formula from scratch.

If this helps, please click the yes button

Cheers,
Shane Devenshire

"MarcoKoenders" wrote in message
...
Currently I am setting up a sheet and I am using the conditional sum
wizard
for formulas. Once a formula is created the sytem won't let me copy or
change
a formula. If I try to copy (Copy, paste special) a formula to another
cell,
the formula won't work anymore. If I create a formula with the wizard and
afterwards change one of the parameters, the formula does not work
anymore.

Can anyone give me a hint on how I can solve these issues? Thanks.