Conditional Sum Wizard

[MarcoKoenders]

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.

 

[Pete_UK]

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

 

[Martin G]

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.

 

[Martin G]

Hi Pete,

Your suggestion has worked out fine.

Thanks a lot.

Regards, Martin

 

[Pete_UK]

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

 

[Pete_UK]

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

Pete

 

[Shane Devenshire]

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