Excel 2003 alternative for SUMIFS()

[prateek.17]

Hi,
I am using a formula in excel 2007 and i want it to work in excel 2003 as well and i need some help.
SUMIFS('Area Pivot'!O:O,'Area Pivot'!$A:$A,$B8,'Area Pivot'!$B:$B,$B$2))

Thanks

 

[T. Valko]

Hi,
I am using a formula in excel 2007 and i want it to work in excel 2003 as well and i need some help.
SUMIFS('Area Pivot'!O:O,'Area Pivot'!$A:$A,$B8,'Area Pivot'!$B:$B,$B$2))

Thanks

Try this...

SUMPRODUCT(--('Area Pivot'!$A1:$A100=$B8),--('Area Pivot'!$B1:$B100=$B$2),'Area Pivot'!O1:O100)

Note that in Excel 2003 you CAN'T use entire columns as range references with the SUMPRODUCT function.

 

[prateek.17]

Thanks a lot.
Can i use more than 3 criteria?
Also, is this going to take longer than sumifs to calculate ?

Many thanks

 

[T. Valko]

Thanks a lot.
Can i use more than 3 criteria?
Also, is this going to take longer than sumifs to calculate ?

Many thanks

Yes and Yes!

In Excel 2003 you can have up to 30 arguments.

The difference in time it takes to calculate depends on several factors:

Size of the ranges referenced
How many arguments you have
How many of these formulas there are

 

[prateek.17]

=IF($B$2="All",SUMIF('Area Pivot'!$A:$A,$B8,'Area Pivot'!E:E),IF($F$5="All",SUMIFS('Area Pivot'!E:E,'Area Pivot'!$A:$A,$B8,'Area Pivot'!$B:$B,$B$2),SUMIFS('Area Pivot'!E:E,'Area Pivot'!$A:$A,$B8,'Area Pivot'!$B:$B,$B$2,'Area Pivot'!$D:$D,Area!$F$5)))

Could you help me with this formula, i want to use an alternative in excel 2003

 

[prateek.17]

=IF($B$2="All",SUMIF('Area Pivot'!$A:$A,$B8,'Area Pivot'!E:E),IF($F$5="All",SUMIFS('Area Pivot'!E:E,'Area Pivot'!$A:$A,$B8,'Area Pivot'!$B:$B,$B$2),SUMIFS('Area Pivot'!E:E,'Area Pivot'!$A:$A,$B8,'Area Pivot'!$B:$B,$B$2,'Area Pivot'!$D:$D,Area!$F$5)))

Could you help me with this formula, i want to use an alternative in excel 2003

 

[Robert Mika]

This should work
=IF($B$2="All",SUMIF('Area Pivot'!$A:$A,$B8,'Area Pivot'!E:E),IF($F$5="All",SUMPRODUCT(--('Area Pivot'!$A:$A=$B8),--('Area Pivot'!$B:$B=$B$2)*('Area Pivot'!E:E)),SUMPRODUCT(--('Area Pivot'!$A:$A=$B8),--('Area Pivot'!$B:$B=$B$2),--('Area Pivot'!$D:$D=Area!$F$5)*'Area Pivot'!E:E)))

But I do not recommend to use whole column ranges(A:A) unless really necessary as it takes long to calculate.
Beside some of your criteria are within the array ranges(like B within B:B)
The formula will calculate but the syntax looks really "odd".

 

[T. Valko]

=IF($B$2="All",SUMIF('Area Pivot'!$A:$A,$B8,'Area Pivot'!E:E),IF($F$5="All",SUMIFS('Area Pivot'!E:E,'Area Pivot'!$A:$A,$B8,'Area Pivot'!$B:$B,$B$2),SUMIFS('Area Pivot'!E:E,'Area Pivot'!$A:$A,$B8,'Area Pivot'!$B:$B,$B$2,'Area Pivot'!$D:$D,Area!$F$5)))

Could you help me with this formula, i want to use an alternative in excel 2003

Try this...

=IF($B$2="All",SUMIF('Area Pivot'!$A:$A,$B8,'Area Pivot'!E:E),IF($F$5="All",SUMPRODUCT(--('Area Pivot'!$A2:$A10=$B8),--('Area Pivot'!$B2:$B10=$B$2),'Area Pivot'!E:E),SUMPRODUCT(--('Area Pivot'!$A2:$A10=$B8),--('Area Pivot'!$B2:$B10=$B$2),--('Area Pivot'!$D2:$D10=Area!$F$5),'Area Pivot'!E2:E10)))

Adjust the ranges to suit in the SUMPRODUCT functions. Just remember that you can't use entire columns as range references with SUMPRODUCT in Excel versons 2003 and earlier.

 

[prateek.17]

Thanks Guys,
Great help !!

 

[T. Valko]

Thanks Guys,
Great help !!

You're welcome!