Sumifs array question

[cuddy89]

I would be very grateful for assistance with the following query which I have spent a while trying to figure out (on a Sunday!)

Please see the attached spreadsheet which contains some sample data.

In column Z, I require columns G,L,N and Q to be summed based on the date to their right, so H,M,P and R.

This is conditional on the below:

There being a date in column U (so non-blank).

The date in column U being greater or equal to the date in column X

The dates in H,M,P and R need to be less than or equal to the date in column Y

Where I am coming unstuck is when column U's date is earlier than the date in any of H,M,P and R

For example, for row 2, £15,366.33 would be the value given in cell Z2, but then £29,636.47 would be given in cell Z5 for row 2.

I think I need to write a sumifs formula in an array for this, but I am struggling to work out how to write this.

Thanks in advance.

Richard

 

[RoryA]

So that makes the date in U irrelevant? Or only where it's less than or equal to the date in Y?

 

[cuddy89]

The value in U is irrelevant if it is blank (no date), or if the date in U is greater than the value in Y (as this will be summed on a future week)

The spreadsheet is tracking the value of stock receipted in to our warehouse. Z can only sum (receipt in) the various components of the stock once there is a value in U, and the date paid of the stock components is less than or equal to the receipted in date
The condition I missed before (thank you for your patience with this) is when U is in an earlier week than some of the stock components were paid, as is the case with the 'Total payment' value in row 2.
In this instance, the formula will need to sum where X is greater than U, and the date paid columns are in the range X to Y