VBA SUMIF with changing ranges and criteria based on string

Matador_24

Board Regular
Joined
Dec 1, 2011
Messages
201
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-0001Land1,399,088.740.000.001,399,088.740.00
001001-0002Building5,596,354.830.000.005,596,354.830.00
001001-0006Apartment Renovations3,323.350.000.003,323.350.00

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
002500-9998Due To/(From) Related Entities Other2,249.000.000.002,249.000.00
002800-0505Tenant Security Deposits Payable-23,254.050.000.00-23,254.050.00
003140-9998Capital Contribution Other-30,179,641.090.000.00-30,179,641.090.00
003300-9998Retained Earnings-1,204,577.28367,573.290.00-837,003.99367,573.29
003300-9999Current Year Earnings367,573.290.00367,573.290.00-367,573.29
003320-9998Distributions To Owners Other24,112,409.680.000.0024,112,409.680.00
004101-0200Straight Line Rental Income0020,122-20,122-20,122.00
004125-0010Real Estate Tax Escalation Commercial Tenants-2,95001,259-4,209-1,259.10
004201-0060Base Rent Residential Tenants-4,100,78801,396,050-5,496,838-1,396,049.60

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
005004-0012Overtime Building Superintendent2,7031,33404,0371,333.51
005004-0013Vacation Pay Building Supintendent5,429005,4290.00
005004-0014Sick Pay Building Superintendent2,110002,1100.00

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

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
Assuming data begin in row 1 maybe

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

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,512
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top