Using two dynamic arrays to concatenate sum ifs in a worksheet formula

[Smurphster16]

Hi ,

I have written a piece of VBA code which creates two dynamic arrays (array1 comprises of dates and array2 monetary values).

I want to use the two dynamic arrays to create a formula in my excel worksheet that adds together several sumif formulas. The number of sumif formulas will be the same as the size of both dynamic arrays (which is the same size as the value of payment dates which I have previously defined)

The sum ifs in the formula need to use the first value of array1 as the range criteria for the first sumif and the first value of array 2 as the sum range for the first sumif and so on.

preferably I would enter the combined sumif formula as a formula into my worksheet rather than calculating in visual basic, please find the first snippet of my code below.

ReDim array1(payment_dates)
ReDim array2(payment_dates)

d = -8 + (-(payment_dates * 4))
e = -5 + (-(payment_dates * 4))
For i = 1 To payment_dates
d = d + 4
e = e + 4
array1(i) = Range("bn86").Offset(0, d)
array2(i) = Range("bn86").Offset(0, e)

Is there a way to enter the formula using ActiveCell.FormulaR1C1? and a loop inside of this? as payment dates could vary every time the code is executed?

Thanks in advance ,

Phil

 

[StephenCrump]

preferably I would enter the combined sumif formula as a formula into my worksheet rather than calculating in visual basic ...

That sounds lke the better approach, but can you post a screenshot illustrating what you are trying to do?

From your code, it looks as if you have a dynamic number of 4-column blocks of data to the left of BN86, with a date in the first column, and an amount in the fourth column of each block.

But it's not clear what you want to do with this data, or whether a SUMIF is even the right formula.

 

[Smurphster16]

Hi Stephen,

Yes that's correct

I want to enter a formula which for each for coulmn block sums the value in the fourth column if the date in the first column of the 4 column block is before a fixed date specified elsewhere in my worksheet - something like this -

=SUMIF(PD(1),"<="&$L$2,MV(1))+SUMIF(PD(2),"<="&$L$2,MV(2)))+SUMIF(PD(3),"<="&$L$2,MV(3))+SUMIF(PD(4),"<="&$L$2,MV(4))

 

[StephenCrump]

I would use a formula along these lines:

TestDate: = $C$2
T4: =SUMPRODUCT((C4:O4<=TestDate)*(MOD(COLUMN(C4:O4)-COLUMN(C4)+1,4)=1),F4:R4)

You may need to put in other checks, e.g. if it is possible to have numeric values with blank dates?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		TestDate	15 Mar 2019
3																				RESULTS
4			15 Feb 2019			999	12 May 2019			888	15 Feb 2019			777	1 Apr 2019			666		1,776
5							3 Apr 2019			222					1 Jan 2019			333		333
6											15 Mar 2019			444						444
Sheet1

Using code, perhaps:

Dim ColOffset As Long, NoRows As Long, NoBlocks As Long, BlockWidth As Long
Dim rngStart As Range, rng1 As Range, rng2 As Range

ColOffset = 2  'no of columns between last value and formula
NoRows = 3  'you can determine dynamically
NoBlocks = 4  'you can determine dynamically
BlockWidth = 4
Set rngStart = Range("T4")  'say

Set rng1 = rngStart.Offset(, -(ColOffset + NoBlocks * BlockWidth - 1)).Resize(, (NoBlocks - 1) * BlockWidth + 1)
Set rng2 = rng1.Offset(, BlockWidth - 1)
rngStart.Resize(NoRows).Formula = "=SUMPRODUCT((" & rng1.Address(0, 0) & "<=TestDate)*(MOD(COLUMN(" & rng1.Address & ")-COLUMN(" & rng1(1).Address & ")+1," & BlockWidth & ")=1)," & rng2.Address(0, 0) & ")"

Or you could use a .FormulaR1C1 approach to specify the column offsets RC[xx]

 

[Smurphster16]

Hi,

This works perfectly, thanks very much!

 

[Smurphster16]

Sorry,

I actually have one query with this , when i run the code, one range is fixed - the first column range within the mod formula in all rows below the first one - how do i get this to change with the row number?

thanks for your help

 

[StephenCrump]

It doesn't really matter that the row number doesn't update, as we're using the Column() function to check that the column number is 1, 5, 9, 13 etc, i.e. that we have a date field, and this doesn't depend on the row number.

But if you do want to update the row number, you could use:

rngStart.Resize(NoRows).Formula = "=SUMPRODUCT((" & rng1.Address(0) & "<=TestDate)*(MOD(COLUMN(" & rng1.Address(0) & ")-COLUMN(" & rng1(1).Address(0) & ")+1," & BlockWidth & ")=1)," & rng2.Address(0) & ")"

When we specify .Address(0), the zero first argument makes the row references relative, turning off the default True value which makes the row references absolute.