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

Smurphster16

New Member
Joined
Feb 28, 2019
Messages
25
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.


Code:
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
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))
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRST
1
2TestDate15 Mar 2019
3RESULTS
415 Feb 201999912 May 201988815 Feb 20197771 Apr 20196661,776
53 Apr 20192221 Jan 2019333333
615 Mar 2019444444
Sheet1


Using code, perhaps:

Code:
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]
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top