VBA SUMIF with changing ranges and criteria based on string

[Matador_24]

Hello,

I have been trying to put together a code but I do not find the way to do it.
I have transactions with an account number (Column A), the values I want to SUM are in column G.
Basically, I need to sum all values in column G which Account Number starts with a 004 (e.g. 004101-200 and above in the example below) and more (see red in the table below). Another complication is that the ranges change. They will always start at row 4 but the last row will be different each time. I already have the code to find the last row, let's call it: FINDLASTROW.
I need the value of the sumif to be in a variable,

How can I put together the code for the SUMIF with the above conditions?

Many thanks in advance!

Luis


A B C D E
[TABLE="width: 995"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]001001-0001[/TD]
[TD]Land[/TD]
[TD="align: right"]1,399,088.74[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1,399,088.74[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]001001-0002[/TD]
[TD]Building[/TD]
[TD="align: right"]5,596,354.83[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]5,596,354.83[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]001001-0006[/TD]
[TD]Apartment Renovations[/TD]
[TD="align: right"]3,323.35[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]3,323.35[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 995"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]002500-9998[/TD]
[TD]Due To/(From) Related Entities Other[/TD]
[TD="align: right"]2,249.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]2,249.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]002800-0505[/TD]
[TD]Tenant Security Deposits Payable[/TD]
[TD="align: right"]-23,254.05[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]-23,254.05[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]003140-9998[/TD]
[TD]Capital Contribution Other[/TD]
[TD="align: right"]-30,179,641.09[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]-30,179,641.09[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]003300-9998[/TD]
[TD]Retained Earnings[/TD]
[TD="align: right"]-1,204,577.28[/TD]
[TD="align: right"]367,573.29[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]-837,003.99[/TD]
[TD="align: right"]367,573.29[/TD]
[/TR]
[TR]
[TD]003300-9999[/TD]
[TD]Current Year Earnings[/TD]
[TD="align: right"]367,573.29[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]367,573.29[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]-367,573.29[/TD]
[/TR]
[TR]
[TD]003320-9998[/TD]
[TD]Distributions To Owners Other[/TD]
[TD="align: right"]24,112,409.68[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]24,112,409.68[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]004101-0200[/TD]
[TD]Straight Line Rental Income[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20,122[/TD]
[TD="align: right"]-20,122[/TD]
[TD="align: right"]-20,122.00[/TD]
[/TR]
[TR]
[TD]004125-0010[/TD]
[TD]Real Estate Tax Escalation Commercial Tenants[/TD]
[TD="align: right"]-2,950[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,259[/TD]
[TD="align: right"]-4,209[/TD]
[TD="align: right"]-1,259.10[/TD]
[/TR]
[TR]
[TD]004201-0060[/TD]
[TD]Base Rent Residential Tenants[/TD]
[TD="align: right"]-4,100,788[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,396,050[/TD]
[TD="align: right"]-5,496,838[/TD]
[TD="align: right"]-1,396,049.60[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 995"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]005004-0012[/TD]
[TD]Overtime Building Superintendent[/TD]
[TD="align: right"]2,703[/TD]
[TD="align: right"]1,334[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4,037[/TD]
[TD="align: right"]1,333.51[/TD]
[/TR]
[TR]
[TD]005004-0013[/TD]
[TD]Vacation Pay Building Supintendent[/TD]
[TD="align: right"]5,429[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5,429[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]005004-0014[/TD]
[TD]Sick Pay Building Superintendent[/TD]
[TD="align: right"]2,110[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2,110[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Assuming data begin in row 1 maybe

Dim FINDLASTROW As Long, dbTotal As Double

FINDLASTROW = Cells(Rows.Count, "A").End(xlUp).Row
dbTotal = Evaluate(Replace("=SUMPRODUCT(--(Left(A1:A@,3)>=""004""),G1:G@)", "@", FINDLASTROW))
'just to check
MsgBox dbTotal

Hope this helps

M.

 

[Matador_24]

It works great, thanks a lot!!!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.