Shortening a Formula

There are three easiest ways to do it (you probably have headers in row1, don't you?):
1) DSUM formula
2) Using autofilter and then SUBTOTAL formula
3) Pivot Table based on your data

The more tough way would be using SUMPRODUCT function, and (aspecially if you have large dataset - could be quite slow).

Hi,

Yes - you can shorten this formula:

=SUM(SUMIFS('Data - CURRENT'!X:X, 'Data - CURRENT'!G:G, {"Service Complete","Service Incomplete","1st Level Invoice Approval","Pend","Assigned","Pend Site Review","Accounting Review","Bill Payment Pend.","In Progress","Invoice Processed","Paid Not Invoiced"}, 'Data - CURRENT'!E:E, "Area 01", 'Data - CURRENT'!I:I, "Building Repairs"))

However, even better would be to store the column G variables somewhere else and store that as a Named Range. For example, if E16:E26 housed these 11 possibilities, and you were to go to Name Manager and define a new name, ColG_Values say, as:

=$E$16:$E$26

your formula would then become simply:

=SUMPRODUCT(SUMIFS('Data - CURRENT'!X:X, 'Data - CURRENT'!G:G, ColG_Values, 'Data - CURRENT'!E:E, "Area 01", 'Data - CURRENT'!I:I, "Building Repairs"))

Regards

You're welcome!