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?

 

[parry]

Im not entirely sure I understand what your asking but if a cell contains Apple mixed with other text and you want to sum the values where the word Apple is contained anywhere in the cell then try using wildcards...

=SUMIF(A2:A7,"*Apples*",C2:C7)

 

[jasonconlon]

Sorry, all the spaces were of course ripped out by the HTML parsing.

Try this, ignoring the dashes which are there in place of spaces.

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

Remember, Apples is a merged (and vertically centred, in this depiction) cell from A2-to-A4, and Bananas is likewise from A5-to-A7...

 

[jasonconlon]

...but no, I must have confused you because in this scenario "Apples" is not combined with any other text -- just the cells A2-to-A4 have a merged format, which contains the text "Apples".


If this isn't possible, then can anyone suggest an elegant macro to run down that first column, copying text ("Apples") it finds there and pasting it into all "empty" cells until it runs into new text ("Bananas") and continuing the process - but remembering to check for adjacent data so it knows when to stop (i.e. row 8 contains no data and should halt the process)..?

That is, to get the data looking like:

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

so that I can use that SUMIF formula successfully...

 

[parry]

Not very fancy Im afraid but presuming you always have 3 cells merged then this may work. Someone may have a more elegant solution.

Book2
	A	B	C	D	E	F
1	Fruit	Type	Total
2	Apple	Red	100		Apple	430
3		Green	150		Banana	210
4		Yellow	180
5	Banana	Green	60
6		Yellow	120
7		Pink	30
Sheet1

 

[jasonconlon]

If only it were that easy!

Unfortunately the real data could have any number of rows associated with the merged cell, and worse still that same named merge cell could pop up again later in the list (e.g. Apples = 3 rows, Bananas = 2 rows, Apples = 12 rows, Oranges = 1 row, etc...) - and all instances of Apples need to go into the total sum.

Interesting attempt, though. You've given me ideas for other conundrums I'm currently working on, but unfortunately not suitable for this particular problem. I really appreciate the effort!

 

[parry]

mmm Im not sure how that could be achieved with a formula. A macro should be able to do it though, so I'll have a look into it after dinner.

 

[jasonconlon]

It would all be so simple if Excel treated each cell in a merged cell as containing the same data...

 

[parry]

See if this works for you. If so, I will see if I can get this to work within a function. Run the RunMacro procedure which in turn runs the Test procedure. Place in a standard module.

Sub Test(Rng As Range, Criteria As String)
Dim Cell As Range, FirstAddress As String, MySum As Double
Dim c As Range

Set Cell = Rng.Find(Criteria, LookIn:=xlValues)

If Not Cell Is Nothing Then
        FirstAddress = Cell.Address
        Do
            If Cell.MergeCells = True Then
                For Each c In Cell.MergeArea
                MySum = MySum + c.Offset(0, 2).Value 'this means 2 columns to the right of the merged cell
                Next c
            Else
                MySum = MySum + Cell.Offset(0, 2).Value 'this means 2 columns to the right of the merged cell
            End If
            Set Cell = Rng.FindNext(Cell)
            If Cell Is Nothing Then Exit Do
        Loop While Cell.Address <> FirstAddress
End If

MsgBox MySum

End Sub


Sub RunMacro()
Dim MyRng As Range
Set MyRng = Range("A1:A10")  'Change this to match your range of the merged cells column
Call Test(MyRng, "Apple") 'looking for apples
End Sub

 

[jasonconlon]

Yep, that does the right thing, and gives a correct result. If you can turn that into a formula, it would be great!

Failing that, I would need help to develop a macro that:
1) finds the first merged cell anywhere in the worksheet
2) takes note of the range of cells covered by that merged cell
3) and copies the value from the first cell in that merged range to all the other cells in that range (and subsequently unmerging the cells)
4) repeats the 1-to-3 cycle until there are no more merged cells to be found
...so that I don't have to deal with the "merged cell" problem.