SUMIF and merged cells

[jasonconlon]

I'm trying to do a SUMIF, but the problem I have is that the criteria I am trying to match is in a merged cell - and only the rows corresponding with the top of that merged cell are considered a match.

For instance, imagine the following 3x7 table, where Apples is a merged cell from A2-to-A4, and Bananas is a merged cell from A5-to-A7:

Type Total
Red 100
Apples Green 150
Yellow 80
Green 60
Bananas Yellow 120
Pink 30

If I want to sum all the apples using =SUMIF(A2:A7,"Apples",C2:C7) then I only get a result of 100 (C2) instead of 330 (C2+C3+C4).

Without unmerging cells, is it possible to write a formula to match and sum these correctly?

 

[jasonconlon]

Just thinking out loud here, if it helps anyone...

Merging is just a formatting option. So the table with merged values referred to initially could equally be thought of as a table without merged values, that looks like:

------------Type---Total
Apples-----Red-----100
------------Green---150
------------Yellow---80
Bananas--Green---60
------------Yellow---120
------------Pink-----30

So to sum all the Apples (in consideration that there might have been more "Apples" listings reappear later in the list), what needs to happen is a formula that goes something like:

SUM all the values in C where [(the corresponding value in A is "Apples") or {(the corresponding value in A is null) and (the previous non-null value in A is "Apples")}].

I reckon I could turn most of that into a SUMPRODUCT formula as follows, but I don't know how to go about writing the component to calclulate "the previous non-null value in A" - represented as "???" below:

=SUMPRODUCT((C1:C1000)*((A1:A1000="Apples")+((A1:A1000="")*(???="Apples"))))

 

[parry]

Hi Jason, see if this works for you. Unfortunately I couldn't use Find in the function so I have had to loop through cells which makes it a bit slower.

Syntax
=SUMMERGE(Target Range, Offset Column, Lookup Value)

Target range is the column of cells you wish to look within for the lookup value. This will be the column that has the merged cells.
Offset Column is a number representing the column to Sum. A positive number means that many columns to the right of the Target column, while a negative number represents that many columns to the left.
Lookup Value is the value you are looking for.

eg Your target range is A2:A7, where your looking for apples and you want to sum the total in Column C.

=SUMMERGE(A2:A7,2,"Apple")

Place this code in a standard module.

Public Function SUMMERGE(Target As Range, ColumnOffset As Integer, LookupVal As Variant) As Double
Dim MySum As Double, c As Range, cell As Range
On Error GoTo errhand
Application.Volatile

If Target.Columns.Count > 1 Then GoTo errhand

For Each c In Target
    If IsError(c) Then GoTo nextcell
    If c.Value = LookupVal Then
        If c.MergeCells Then
            If c.Address = c.MergeArea(1).Address Then
                For Each cell In c.MergeArea
                    MySum = MySum + cell.Offset(0, ColumnOffset).Value
                Next cell
            End If
        Else
            MySum = MySum + c.Offset(0, ColumnOffset).Value
        End If
    End If
nextcell:
Next c

SUMMERGE = MySum
Exit Function
errhand:
SUMMERGE = CVErr(1)
End Function

 

[jasonconlon]

That works, but...

Just to add a li'l complexity to the problem --

There are instances where "Apples" only has a single row associated with it - so it's not always a merged cell. This means that my results are coming up short, because the non-merged versions of "Apples" aren't being included.

(Thanks heaps for all your help thus far, Parry, by the way!)

It is a li'l slow, as you mentioned, but I can certainly live with that.

 

[parry]

Worked OK for me. The lookup value needs to be exact so if you are looking for Apple then this will not find "apple", "APPLE", "Apples" or "<space>Apple".

 

[parry]

Worked OK for me. The lookup value needs to be exact so if you are looking for Apple then this will not find "apple", "APPLE", "Apples" or "<space>Apple".

The last bit should have read SPACE then Apple as an example but Im sure you got the gist. Its not hard to change the function to ignore case or to cater for partial matches. Let me know.

 

[jasonconlon]

One thing about the new function, though... Since putting it in place, every time I make a change to any cell in the workbook (even if it's not on the same worksheet where I am using that function), the workbook goes and recalculates it. And because the function isn't very quick, it's made the book painful to work in. But what can you do, 'eh?

 

[parry]

Delete the Application.Volatile line. When you enter in the target range are you entering in a whole column. If so, then empty cells are being evaluated too so this amendment should fix that...

Public Function SUMMERGE(Target As Range, ColumnOffset As Integer, LookupVal As Variant) As Double
Dim MySum As Double, c As Range, cell As Range
On Error GoTo errhand

If Target.Columns.Count > 1 Then GoTo errhand
Set Target = Intersect(Target, ActiveSheet.UsedRange)

For Each c In Target
    If IsError(c) Then GoTo nextcell
    If c.Value = LookupVal Then
        If c.MergeCells Then
            If c.Address = c.MergeArea(1).Address Then
                For Each cell In c.MergeArea
                    MySum = MySum + cell.Offset(0, ColumnOffset).Value
                Next cell
            End If
        Else
            MySum = MySum + c.Offset(0, ColumnOffset).Value
        End If
    End If
nextcell:
Next c

SUMMERGE = MySum
Exit Function
errhand:
SUMMERGE = CVErr(1)
End Function

 

[fairwinds]

Hi,

Would it not be a good idea to add a column (you can hide it) and use SUMIF formulas?

Book1.xls
	A	B	C	D	E	F	G	H
1	Fruit	Type	Total				Fruit
2	Apple	Red	100		Apple		Apple	2100
3		Green	200		Apple		Banana	1500
4		Yellow	300		Apple		Orange	4200
5	Banana	Green	400		Banana
6		Yellow	500		Banana
7		Pink	600		Banana
8	Apple	Blue	700		Apple
9		Black	800		Apple
10	Orange	Red	900		Orange
11		Green	1000		Orange
12		Yellow	1100		Orange
13		Rotten	1200		Orange
Sheet2

 

[jasonconlon]

Unfortunately that bombed straight away with "#VALUE!" results..?
(It sure was a lot quicker though. :wink: )

 

[jasonconlon]

That's an option, Fairwinds, but my particular situation is that my workbook has one sheet where all of this summing is being done, and then a myriad of sheets that look like that Apples and Oranges data.

I'm using variables/functions to look up just the latest sheet, and then to run these sums; and I'd prefer not to have to add that extra column each day when a new data sheet is added to the book.