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

 

[SanjayGMusafir]

I thought someone would reply so I overlooked. Sorry for late response. Try this in Z2 and below...

=IF(AND(U2<>"",U2>=X2,H2<=Y2,M2<=Y2,P2<=Y2,R2<=Y2),G2+L2+N2+Q2,"")

 

[Alex Blakenburg]

You are going to find it difficult to get people to buy into this without providing an XL2BB of your data rather than a picture, that is quite a bit of data to set up to test out a formula. (Sanjay has a link to the XL2BB tool in his signature block)

I don't understand what this means:

but then £29,636.47 would be given in cell Z5 for row 2.

Anyway below is a bit of a brute force approach. Give that a try.

20220926 Add Up column based on assoc dates cuddy89.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Deposit	Date Paid	FOB Total			Total Payment	Date Paid	Duty	VAT	Date Paid	Freight	Date Paid	Deferment fee	Date Paid	Date receipted in			W/C	W/E	Collections	Orig Collections
2	2,910.00	29/07/2022	32,546.47			29,636.47	14/09/2022	4,567.89	12,345.67	19/08/2022	7,654.32	19/08/2022	234.12	19/08/2022	26/08/2022			22/08/2022	28/08/2022	15,132.21	44,768.68
3			12,345.67			12,345.67	14/09/2022	1,234.23	3,026.54	23/08/2022	2,000.00	23/08/2022	42.57	23/08/2022	26/08/2022			29/08/2022	4/09/2022	-	15,579.90
4	3,500.00	5/08/2022	33,500.00			30,000.00	16/09/2022	3,000.00	10,000.00	18/09/2022	3,000.00	18/09/2022	53.42	18/09/2022				5/09/2022	11/09/2022	-	39,500.00
5																		12/09/2022	18/09/2022	-	-
Sheet1

Cell Formulas
Range	Formula
Z2:Z5	Z2	=IF(AND(U2<>"",U2>=X2), (((H2<=Y2)*(H2<>""))*G2 + ((M2<=Y2)*(M2<>""))*L2 + ((P2<=Y2)*(P2<>""))*N2 + ((R2<=Y2)*(R2<>""))*Q2), 0)
AA2:AA5	AA2	=G2+L2+N2+Q2

 

[cuddy89]

Hi Both

Sorry for my delay in replying, I've only just had chance to look at this.

Thank you for your responses.

Sanjay, your formula works when comparing line by line, but what I'm struggling to work in to the formula is summing the range of the H to U columns.

So I would need these to be something like:

=IF(AND(U2:U50<>"",U2:U50>=X2,H2:H50<=Y2,M2:M50<=Y2,P2:P50<=Y2,R2:R50<=Y2),G2:G50+L2:L50+N2:N50+Q2:Q50,"")

To clarify, cell Z2 needs to look at the full range of data, and only X and Y will be cell references, not ranges.

This is why I think I need a SUMIF formula, and possibly an array.

Alex, I need my IT colleague to install XL2BB for me as I don't have permissions (which I'm getting done). I am grateful for you inputting the data for me.

I think my explanation to Sanjay above may answer your query re the £29,636.47 in cell Z5, but to clarify:

As the formula needs to look at the full range of values in the relevant columns from H to U, cell Z5 will pick up the £29,636.47 from cell L2 as this was paid on 14/09/22, which falls in the date range of X5 and Y5.

I hope this helps, apologies if I wasn't clear enough in my original post.

Thanks

Richard

 

[SanjayGMusafir]

cell Z2 needs to look at the full range of data, and only X and Y will be cell references

To the best of my knowledge add a helper column in Column V and get the desired results in Z2 based on that column V

 

[RoryA]

To clarify, cell Z2 needs to look at the full range of data, and only X and Y will be cell references, not ranges.

I'm not sure I follow your desired results then. Why is the first row result seemingly not including the second row of data, which seems to match all the criteria?

 

[cuddy89]

I'm not sure I follow your desired results then. Why is the first row result seemingly not including the second row of data, which seems to match all the criteria?

Sorry, I'm not sure what you mean. Are you referring to Z3? The formula in Z3 on the XL2BB only looks at row 3, which has dates in columns H to U outside the range of X3 to Y3.

If the formula in Z3 looks at the full range of data, Z3 should still be blank as all dates fall outside the X3 to Y3 range, whilst Z5 should contain values from rows 2 and 3

 

[cuddy89]

To the best of my knowledge add a helper column in Column V and get the desired results in Z2 based on that column V

Thanks Sanjay, I will try a workaround now with a helper column.

 

[RoryA]

Sorry, I'm not sure what you mean. Are you referring to Z3? The formula in Z3 on the XL2BB only looks at row 3, which has dates in columns H to U outside the range of X3 to Y3.

If the formula in Z3 looks at the full range of data, Z3 should still be blank as all dates fall outside the X3 to Y3 range, whilst Z5 should contain values from rows 2 and 3

No I mean why isn’t Z2 including the data from row 3? You said you wanted each formula to include all matching rows of data.

 

[cuddy89]

No I mean why isn’t Z2 including the data from row 3? You said you wanted each formula to include all matching rows of data.

That is what I am struggling with - both answers above only compare the formula in column Z with the corresponding row.

I would be grateful for any advice on this, I'm still struggling to make it work