Alternative to an if sumproduct formula

I have the following formula in Cells G69:G439.

=IF($D$53="Service Code",SUMPRODUCT(($D$52='Actuals Input'!$AG$4:$AG$27885)*('Actuals Input'!$I$4:$I$27885>700000)*('Actuals Input'!$L$4:$L$27885<>"HI1")*($F69='Actuals Input'!$X$4:$X$27885)*('Actuals Input'!$C$4:$C$27885)),SUMPRODUCT(($D$52='Actuals Input'!$AG$4:$AG$27885)*('Actuals Input'!$I$4:$I$27885>700000)*('Actuals Input'!$L$4:$L$27885<>"HI1")*($F69='Actuals Input'!$V$4:$V$27885)*('Actuals Input'!$C$4:$C$27885)))

I would like to replace this formula with something more efficient and faster at calculating.

Can anyone please help?

Since you have XL2007 you can use COUNTIFS formula that will be faster.

Brumbot/Zbor,

Many thanks for your swift response.

I have already tried using SUMIFS, but it did not work. It may be that I did something wrong, so will try again.

Trax

Having re-tried SUMIFS, I am getting 'The formula you typed contains an error'. I have tried various different ways, but don't ssem to get anywhere.

=IF($D$53="Service Code",SUMIFS('Actuals Input'!$C$4:$C$9886,$D$52,'Actuals Input'!$AG$4:$AG$9886,'Actuals Input'!$I$4:$I$9886,">700000",'Actuals Input'!$L$4:$L$9886,"<>HI1",$F$69,'Actuals Input'!$X$4:$X$9886)),SUMIFS('Actuals Input'!$C$4:$C$9886,$D$53,"Service Code",$D$52,'Actuals Input'!$AG$4:$AG$9886,'Actuals Input'!$I$4:$I$9886,">700000",'Actuals Input'!$L$4:$L$9886,"<>HI1",$F$69,'Actuals Input'!$V$4:$V$9886)))

What am I doing wrong?

Formula:

Please Login or Register  to view this content.

Lots of things.. check did I manage to fix it.

Zbor,

It still gives the error message!

Just try to follow syntax:

Formula:

Please Login or Register  to view this content.

oh, yes, keep you ranges same:

'Actuals Input'!$C$4:$C$9886 vs. 'Actuals Input'!$AG$4:$AG$27885

they all must be same size!

Formula:

Please Login or Register  to view this content.

I have gone through the setps, which independently seem to work fine. When I put them together, it tells me that there are too few arguments.

=IF($D53="Service Code",SUMIFS('Actuals Input'!$C$4:$C$9886,'Actuals Input'!$AG$4:$AG$9886,$D$52,'Actuals Input'!$I$4:$I$9886,">700000",'Actuals Input'!$L$4:$L$9886,"<>HI1",'Actuals Input'!$X$4:$X$9886,$F69)),SUMIFS('Actuals Input'!$AG$4:$AG$9886,$D$52,'Actuals Input'!$I$4:$I$9886,">700000",'Actuals Input'!$L$4:$L$9886,"<>HI1",'Actuals Input'!$V$4:$V$9886,$F69))

I think there is one extra bracket:

Formula:

Please Login or Register  to view this content.

There should be 2 at the end. YOu have one extra in the middle (try mine solution above):

=IF($D53="Service Code",SUMIFS('Actuals Input'!$C$4:$C$9886,'Actuals Input'!$AG$4:$AG$9886,$D$52,'Actuals Input'!$I$4:$I$9886,">700000",'Actuals Input'!$L$4:$L$9886,"<>HI1",'Actuals Input'!$X$4:$X$9886,$F69)),SUMIFS('Actuals Input'!$AG$4:$AG$9886,$D$52,'Actuals Input'!$I$4:$I$9886,">700000",'Actuals Input'!$L$4:$L$9886,"<>HI1",'Actuals Input'!$V$4:$V$9886,$F69))