Array Formula with Sumif and between dates

Try

=SUMIF(B2:B24,">="&F7,C2:C24)-SUMIF(B2:B24,">"&F8,C2:C24)

where F7 contains start date and F8 contains end date

In what way... the ranges are different in your array formula and the other formula?

What exactly are all the conditions you want met and what is to be summed in the end?

No, not really.

Here is the basic syntax you can try

=Sumproduct(--(Range1=Criteria1),--(Range2=Criteria2),...etc, --(RangeX>=StartDate),--(RangeY<=EndDate),SumRange)

This formula is not a CSE formula. That means, just confirm with ENTER only...

Which column are the dates in? And where is the Start/End date cells?

... ah, a fellow Colcestrian.

If you're using XL2007 as implied by your profile you could use SUMIFS function which is like SUMPRODUCT is not an Array but is accepted to be more efficient... though not backwards compatible with pre XL2007 versions.

Please Login or Register  to view this content.

replace the ? with references as appropriate for the date range / criteria dates - not clear.

Note: even though the above is more efficient than either SUMPRODUCT/Array given the range volume it will still be quite slow.

]Basically the same as post # 6... just fill in the blanks...

Sumproduct:

Please Login or Register  to view this content.

Neil, I think both NBVC & I have resolved your problem you just need to tell us in which column your dates are stored on the 'SAP Data Current' sheet - you may have done so already but I'm afraid we've not noticed.

Try and be as clear as possible... remember we can't see your file and thus are basing everything entirely off the narrative you provide - the clearer you are regards the ranges the more tailored our response can be - ie plug & play solution.

Up the U's

Remember though that the SUMIFS won't work pre XL2007... if that's an issue you should revert to the SUMPRODUCT (will perform perhaps marginally quicker than the Array but does not require CTRL + SHIFT + ENTER entry so is more robust from that perspective...)

Other new to XL2007 formulae that may become of interest to you would be: COUNTIFS, AVERAGEIF, AVERAGEIFS - most of which can avert need for Arrays as required in earlier versions...