Summing Array Variable by Another Value in the Array (VBA Code)

I have an array in memory containing user entered data.

I know the spreadsheet formula is =SUMIF(F$13:F$16,F28, I$13:I$16). How do I write this in VBA Code?

I need the total of the feevalue by date (see attached spreadsheet). It should remain in memory so I can compare it to a DailyMax amount and adjust the feevalue accordingly.

Here is one example:

Please Login or Register  to view this content.

Well the problem is that nothing is in the cells yet as the array in entirely in memory at this point.
I did put the values into cells to write the Sumif() formula within a cell to get the syntax down.

Which array are you trying to sum?

The feevalue(i) to be summed if the iDate(i) are = to each other.

I apologize for not being more clear.
I think the latter is the one I am looking for. I will give it a whirl and let you know.

I appreciate the help.

I am not sure how to use the Function correctly. Also I don't want to have to enter a date. The dates are already in the array.

Say I enter as pictured it should sum by the date as color coded. The data is entered into an array in memory only.

Attachment 153830

Somewhere in your code, you would need to add something like:

Please Login or Register  to view this content.

Since iDate is an array containing many dates, you would need to tell the function for which dates you would like to sum, yes? I'm assuming that the values in the array feevalue and the dates in the array iDate are coordinated (they were both the same size). The function merely strolls down the elements in the array iDate looking for a date and when it finds that date it then adds the values in freevalue together:

Please Login or Register  to view this content.

Note that I'm assuming that for each element (i) in iDate, there is a coordinated value in feevalue. So that feevalue(i) was a fee/charge on iDate(i).

Thus your image suggests that you need:

Please Login or Register  to view this content.

Where dbSumX represents where you want the sum of the fees for each date. Of course, you don't want to hard code the dates like: #6/22/2010#, but somehow you need to tell the function for which dates you want the sum of feevalue, yes?

Is this making sense, or am I totally off base?

The array consists of the user input revcode(i), hcpc(i), modifier(i), iDate(i), units(i) & charges(i) where i is a user input Integer for line items on a medical bill. The VBA code determines feevalue(i) based on several different Vlookup()s, etc. Then certain rules have to be applied. There is a DailyMax amount (if the hcpc(i) is not exempt). Feevalue(i) cannot exceed that DailyMax for each date. There could be 1 date of service, there could be several but each is contained in the iDate(i) array.

So I am looking for the function/macro to find each unique date (as there will be multiple feevalue(i) for each date), total the feevalue(i) for each unique date and compare it to the DailyMax amount.

Instead of coding a date or entering it a second time the code should parse out each unique date from the array, if that's possible.

Please Login or Register  to view this content.

This code is merely an example of how this might be done. You really don't need: HowManyDifferentDatesInDateArray

But DatesInDateArray creates a list of dates.

In you code you could add:

Please Login or Register  to view this content.

The string sList will contain a list (separated by a comma) of dates from iDate (one item per date).

Next:

Please Login or Register  to view this content.

This will put each date in a string array.

Now assuming that for each element (i) in iDate, there is a coordinated value in feevalue.

Please Login or Register  to view this content.

For each element (i) in sArray, you can find the sum in feevalue (assuming that each element in feevalue corresponds to an element in iDate).

Sorry about that.

Please Login or Register  to view this content.

The problem is your iDate is a Variant array, whereas I wrote "DatesInDateArray" as an array of dates.

If you will go back through my code and change "As Date" to "As Variant" that should correct the problem.

Such as:

Please Login or Register  to view this content.

Not your fault and no apologies necessary. I am very grateful for your help.

Wouldn't dbSum need to be an array that corresponds with sList? Otherwise it's only getting the value for the last date in the sList array.

I think I am executing it in the wrong place too... it should be in a loop(?) defined by number of dates in the array, not how many line items on the bill....

Please Login or Register  to view this content.

Perhaps something like this:

Please Login or Register  to view this content.

Wouldn't dbSum need to be an array that corresponds with sList?

Yes.

I was thinking that you might be adding these values somewhere in your worksheet. Thus every time you loop you could just add it to your worksheet.

But if you prefer to have it as an array, then perhaps something like this:

Please Login or Register  to view this content.

That worked out perfectly...

I'll probably screw it up when I add the final tweaks...

Thank you for everything. Not only did you post the code, but you gave me an explanation of what it did to help me learn. I appreciate it.

You're welcome. I'm glad things worked out.

really really useful post and reply

Loads of other threads have just advise to sum using loops, but the .Index solution is a LOT more efficient

Gjoka:

If you have a better solution to the question, feel free to post it along with instructions on why/how the codes work and why you think it's a better option.

Teach don't troll.