SUMIFS Alternative

[Simon1185]

Morning guys,

I've been looking for an alternative to SUMIFS as I have a sheet I need backwardly compatible with Excel 2003. I've looked at SUMPRODUCT which I understand is a good alternative to COUNTIFS but does not allow for summing. I've now moved on to looking at an array formula I picked up from an internet search, but it's not working properly for me. The crux of my Excel 2007 formula is:

=SUMIFS(Estimates!H:H,Estimates!A:A,A2,Estimates!P:P,"")

The array formula suggested would seem to tranlate as follows:

=SUM(IF((Estimates!A2:A1000=A2)*(Estimates!P2:P1000=""),Estimates!H1:H1000,0))

However all this is doing is summing the whole H:H range, regardless of the conditions. (note, I've only shortened the range of cell in the array formula as the place I found it noted that including a whole colmun would slow the sheet to a crawl).

Any ideas? Many thanks in advance.

Simon.

[DonkeyOte]

Sumproduct can COUNT/SUM so not quite sure where you picked that up from...

=SUMPRODUCT(--(Estimates!A2:A1000=A2),--(Estimates!P2:P1000=""),Estimates!H2:H1000)

keep ranges to a minimum - preXL07 you can't use entire column references (ie A:A) and nor should you (for the reason you state yourself).

For more info on SUMPRODUCT see the link in my sig to Bob Phillips' white paper.


I would as I always do make the point that using large numbers of SUMPRODUCTS will tend to lead to poor performance, you can by means of concatenation avoid need for Arrays ... ie

Estimates!Z2 =$A2&":"&$P2
copied down to Z1000

Then you can just use SUMIF

=SUMIF(Estimates!$Z$2:$Z$1000,$A2&":",Estimates!$H$2:$H$1000)

much more efficient when used in large volume.

[Simon1185]

Thanks guys, that's done the trick. The white paper you mentioned DonkeyOte was a big help.