Solved: SUMIF attempting to interrogate cell properties in "criteria" portion of the syntax

[CareerChange]

Hello,

I have a worksheet that has 1000+ non-adjacent named cells that contain detail data that needs to be summarized via multiple sub categories.

Each of these named cells adhere to a strict naming convention, and I've attempted to use =SUMIF (w absolutely no luck, hence this question) to locate all of the cells on the worksheet that match a portion of the cell's name that I need to summarize. For instance...

Sample cell names...
rskgbwSales - contains Sales detail data for a given quarter
rskgbwSalesM1 - contains Sales detail data for the 1st MM of the qtr
rskgbwSalesM2 - ... for 2nd MM of qtr
rskgbwSalesM3 - ... for 3rd MM of qtr
etc, etc.

Here's an example of a particular sub category that needs summarized, and the erroneous SUMIF formula that I attempted to use.

=SUMIF("D6:AO76", (If (InStr(1, Range.Name.Name) = "gbw") And (InStrRev(Range.Name.Name, "M1") > 0) )

If SUMIF can't do what I want, is there an alternative either using VBA or some other built-in function that will solve my problem?

Thank you!

[Bob Phillips]

Try this UDF

[vba]

Public Function SumNamedCells(rng As Range, SubCat As String) As Double
Dim cell As Range
On Error Resume Next
For Each cell In rng

If cell.Name.Name Like SubCat Then

SumNamedCells = SumNamedCells + cell.Value
End If
Next cell

End Function
[/vba]

and use like so

=SumNamedCells(D6:AO76,"*gbw*M*")

[CareerChange]

Thank you so much for the quick and expert reply, and of course, I have some follow-up questions...

Situation: When I paste the UDF into the target summary cell(s) and adjust the 'subcat' for the given total that I'm seeking and hit <enter>, I immediately get the "!" popup menu icon (giving me the choices to 'fix' the error). Simultaneously to receiving that visual, Excel (2003) executes the UDF and it takes about 1 - 1.5 minutes to complete the calculation, and then the "!" popup menu disappears, and I get the expected total.

It did exactly what I had hoped and expected from a calculation stand point, however I'm concerned about the following...

Is that the expected behavior for such a UDF in terms of its initial setting and execution time? If 'yes', then I would guess that it would behave similar to any other built-in Excel function and execute whenever an Excel event change occurs.

And if that's the case, then it would explain why Excel goes into hourglass mode for 5+ minutes whenever I now click on an Outline on that sheet.

So in the end, I got exactly what I needed to simplify what would otherwise be a series of manual, tedious, and possibly error-prone =SUM calculations, but the performance is abysmal.

Did I do something wrong from an Excel point of view when I cut/paste/adjusted the UDF into the cell?

[Bob Phillips]

The problem is that it iterates every cell in your reange, which will be slow in a large range.

Try this version, see if it is better

[vba]

Public Function SumNamedCells(rng As Range, SubCat As String) As Double
Dim nme As Name
On Error Resume Next
For Each nme In ActiveWorkbook.Names

If Not Intersect(nme.RefersToRange, rng) Is Nothing Then

If nme.Name Like SubCat Then

SumNamedCells = SumNamedCells + nme.RefersToRange.Value
End If
End If
Next nme

End Function
[/vba]

[CareerChange]

AWESOME!!! That did it... Thank you!!!

[CareerChange]

The inevitable follow-up after the joy and bliss phase concluded

Here's a little more background that I believe is relevant to this new question...

This particular worksheet where the UDF is placed is divided into 5 major horizontal sections that support each of our Line of Businesses (LOBs). From a visual standpoint, I've judiciously used the Outline group(s) both vertically and horizontally as a way to provide an uncluttered macro financial view of their LOB's, as well as the ability to drill down via the Outlines to a micro financial view of a specific part of their business.

Keeping that design point in mind, I placed the UDF in somewhere between 150 - 175 sub category summary cells across those 5 LOB's. Everything was fine until I hit the outer vertical Outline button in the left margin to visually compress all five LOB sections, and 30+ minutes later, the UDF's are still running, pegging my CPU at 100%. Yikes!! I'm hoping that Excel is not invoking recursive calls during its Event processing, because I do not want to kill this xls, even if I only lose the work since my last "save".

Anyhow... I thought that a way to solve this problem was using the Application.Calculation = xlCalculationManual option, however there seems to be enough gotchas around it usage that it may not solve my problem in all cases. Thoughts?

I was toying w another possible option...

Is it possible to cache all of the names and cell locations into an array during the Workbook_Open_Event and enhance the UDF to work from the cached arrays? Additionally, since all 8 of the worksheets in this workbook are protected, there's no possibility of new rows or columns being added, nor any kind of user input.

As an absolute last resort, I figured that the UDF could be modified to a Sub and have it create the resulting =SUM formula that I could then cut-and-paste into the cell, thereby eliminating all of the problems around my extensive usage of the UDF firing due to Excel event processing.

My apologies for the long post, but I hope that a thoughtful explanation of the problem will help zero in on what the viable options would be given the parameters around my application.

Thank you!!