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

001001-0001	Land	1,399,088.74	0.00	0.00	1,399,088.74	0.00
001001-0002	Building	5,596,354.83	0.00	0.00	5,596,354.83	0.00
001001-0006	Apartment Renovations	3,323.35	0.00	0.00	3,323.35	0.00

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

002500-9998	Due To/(From) Related Entities Other	2,249.00	0.00	0.00	2,249.00	0.00
002800-0505	Tenant Security Deposits Payable	-23,254.05	0.00	0.00	-23,254.05	0.00
003140-9998	Capital Contribution Other	-30,179,641.09	0.00	0.00	-30,179,641.09	0.00
003300-9998	Retained Earnings	-1,204,577.28	367,573.29	0.00	-837,003.99	367,573.29
003300-9999	Current Year Earnings	367,573.29	0.00	367,573.29	0.00	-367,573.29
003320-9998	Distributions To Owners Other	24,112,409.68	0.00	0.00	24,112,409.68	0.00
004101-0200	Straight Line Rental Income	0	0	20,122	-20,122	-20,122.00
004125-0010	Real Estate Tax Escalation Commercial Tenants	-2,950	0	1,259	-4,209	-1,259.10
004201-0060	Base Rent Residential Tenants	-4,100,788	0	1,396,050	-5,496,838	-1,396,049.60

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

005004-0012	Overtime Building Superintendent	2,703	1,334	0	4,037	1,333.51
005004-0013	Vacation Pay Building Supintendent	5,429	0	0	5,429	0.00
005004-0014	Sick Pay Building Superintendent	2,110	0	0	2,110	0.00

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.