SumIF's vs array formula..

[Dtex20]

Array forumla = =SUM(IF('Order Sheet'!$C$7:$C$19358>=Summary!B3,IF('Order Sheet'!$C$7:$C$19358<=Summary!C3,'Order Sheet'!$F$7:$F$19358)))

SUMIFs = =SUMIFS('Order Sheet'!F7:F19358,'Order Sheet'!C7:C19358, ">=B3",'Order Sheet'!C7:C19358, ">=C3")


I have two formula's one array, one a sumif. B3 & C3 contain a date, the formula looks for that date, if true adds the dates between the range in B3:C3.


I tried to remove the array forumla and do a SUMIFs but it doesn't work. The spreadsheet is so slow with the array forumla it's used over 30 times.

Can anyone point to where i'm going wrong?

Thanks

 

[RoryA]

You have the quotation marks in the wrong place:

=SUMIFS('Order Sheet'!F7:F19358,'Order Sheet'!C7:C19358, ">="&B3,'Order Sheet'!C7:C19358, ">="&C3)

 

[AlanY]

Array forumla = =SUM(IF('Order Sheet'!$C$7:$C$19358>=Summary!B3,IF('Order Sheet'!$C$7:$C$19358<=Summary!C3,'Order Sheet'!$F$7:$F$19358)))

SUMIFs = =SUMIFS('Order Sheet'!F7:F19358,'Order Sheet'!C7:C19358, ">=B3",'Order Sheet'!C7:C19358, ">=C3")


I have two formula's one array, one a sumif. B3 & C3 contain a date, the formula looks for that date, if true adds the dates between the range in B3:C3.


I tried to remove the array forumla and do a SUMIFs but it doesn't work. The spreadsheet is so slow with the array forumla it's used over 30 times.

Can anyone point to where i'm going wrong?

Thanks

try

=SUMIFS('Order Sheet'!F7:F19358,'Order Sheet'!C7:C19358, ">="&B3,'Order Sheet'!C7:C19358, "<="&C3)