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]

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

Formula works for me. Ive sent you a PM with my email address so you can send the book if you want and I'll see if I can spot whats wrong.

 

[jasonconlon]

No worries Jason. I see its my fault because I didnt account for the fact that you might be referencing another sheet. Should be OK now but let me know as I havent checked for accuracy..

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, Target.Parent.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

Jackpot, Parry!

You're an absolute genius.

I need to ask one more favour, though, if it's at all possible...

Wouldn't you believe it, the complexity doesn't end there. It turns out that in the target range, one of the values to match (cell B158) isn't coming up as a match because it has gunk after it - a funny whitespace character (10) and a return character (160).

Any idea how to include in SUMMERGE the removal of this gunk before trying to check for a match? i.e. something equivalent to the formula:
=SUBSTITUTE(SUBSTITUTE(B158,CHAR(10),""),CHAR(160),"")

I'm not quite sure how to rewrite the line:

• If c.Value = LookupVal Then

as my guess of:

• If Substitute(Substitute(c.Value, CHAR(10), ""), CHAR(160), "") = LookupVal Then

certainly doesn't work...

 

[parry]

Hi Jason, you can do it with Substitute but Ive just altered the function for you. This is now case insensitive and will accept wildcards.

So say you wanted to find variations of the word Apple you could use in this way to pick up Apple, My Apple,APPLE or Apples etc...

=SUMMERGE(A1:A10,2,"*Apple*")

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, Target.Parent.UsedRange)

For Each c In Target
    If IsError(c) Then GoTo nextcell
    If UCase(c.Value) Like UCase(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

 

[parry]

If you know you have problems with certain cells then you could remove those unwanted characters by using the CLEAN and TRIM functions.

 

[jasonconlon]

Nah, neither CLEAN nor TRIM get rid of those particular characters.

I could use the SUBSTITUTE formula I described earlier, but that means adjusting the new report that comes in every day; whereas if it's built into the summary page formula (into SUMMERGE) then it all happens automatically when the new report sheet is added.

 

[jasonconlon]

BRILLIANT, Parry!

That's the ticket. Perfect, and finito.

I can't thank you enough. I really owe you one. Thank you so very much.

 

[parry]

No worries, have a good weekend.

 

[madcosmonaut]

Hello

Thanks, Parry, for your solution to this issue a few years ago. It was exactly what I needed as well. I'm having a problem though.... when I reference an external worksheet which is closed, the function returns a #VALUE! error.

Is there any way to alter the function to allow it to work with excel files that are not currently open?

Thanks so much in advance for any advice.

 

[jrgsampaio]

This may be quite an old thread, but since I was having the same problem and found a kind of solution without using VBA, I thought it would be worth coming back here and present my solution using Parry's example.
So here it is.
1) unmerge the merged "Apple" cells (A2:A4); select them and give them a name (fill the Name Box at left of the Input Line), say MyApples; now merge them again
2) unmerge the merged "Banana" cells (A5:A7); select them and give them a name, say MyBananas; now merge them again

The situation now is the very same as before, but now the merged cells are "named areas". Now do the following

3) in the cell where you want the sum of the apples (F2) enter: =SUMIFS(C1:C7,ROW(C1:C7),">="&ROW(MyApples), ROW(C1:C7),"<"&(ROW(MyApples)+ROWS(MyApples)))
4) in the cell where you want the sum of the bananas (F3) enter: =SUMIFS(C1:C7,ROW(C1:C7),">="&ROW(MyBananas), ROW(C1:C7),"<"&(ROW(MyBananas)+ROWS(MyBananas)))

You should get 430 apples and 210 bananas, as expected. (I hope!)

Now let's see why it worked:

The SUMIFS will add the elements in C1:C7 (first argument) that satisfy the two next conditions (4 next arguments)
The first condition gets the row of the selected cells (2nd arg) and check if it is larger then or equal to the top cell of the area MyApples (3rd arg) (in this case, 2)
The second condition gets the row of the selected cells (4th arg) and check if it less then the row below the bottom of the area MyApples (5th arg) - (in this case, 2+3=5)

If both conditions are TRUE, the element is included in the sum, because they are within in the rows or the area MyAppples. Therefore it sums the number of apples. The same for Bananas

Note that if you want to add another row for a given fruit you should select any row in the area, except the first, right click and add a row. Then fill the data.
Good luck.
Jorge

 

[jrgsampaio]

This may be quite an old thread, but since I was having the same problem and found a kind of solution without using VBA, I thought it would be worth coming back here and present my solution using Parry's example.
So here it is.
1) unmerge the merged "Apple" cells (A2:A4); select them and give them a name (fill the Name Box at left of the Input Line), say MyApples; now merge them again
2) unmerge the merged "Banana" cells (A5:A7); select them and give them a name, say MyBananas; now merge them again

The situation now is the very same as before, but now the merged cells are "named areas". Now do the following

3) in the cell where you want the sum of the apples (F2) enter: =SUMIFS(C1:C7,ROW(C1:C7),">="&ROW(MyApples), ROW(C1:C7),"<"&(ROW(MyApples)+ROWS(MyApples)))
4) in the cell where you want the sum of the bananas (F3) enter: =SUMIFS(C1:C7,ROW(C1:C7),">="&ROW(MyBananas), ROW(C1:C7),"<"&(ROW(MyBananas)+ROWS(MyBananas)))

You should get 430 apples and 210 bananas, as expected. (I hope!)

Now let's see why it worked:

The SUMIFS will add the elements in C1:C7 (first argument) that satisfy the two next conditions (4 next arguments)
The first condition gets the row of the selected cells (2nd arg) and check if it is larger then or equal to the top cell of the area MyApples (3rd arg) (in this case, 2)
The second condition gets the row of the selected cells (4th arg) and check if it less then the row below the bottom of the area MyApples (5th arg) - (in this case, 2+3=5)

If both conditions are TRUE, the element is included in the sum, because they are within in the rows or the area MyAppples. Therefore it sums the number of apples. The same for Bananas

Note that if you want to add another row for a given fruit you should select any row in the area, except the first, right click and add a row. Then fill the data.
Good luck.
Jorge

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

******** ******************** ************************************************************************><center>[TABLE="align: center"]
<tbody>[TR]
[TD="bgcolor: #0c266b, colspan: 7"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD="align: left"]Microsoft Excel - Book2[/TD]
[TD="align: right"]___Running: xl2002 XP : OS = Windows XP [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 7"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD]File Edit View Insert Options Tools Data Window Help About[/TD]
[TD="align: center"]<form name="formCb057699"><input *******="window.clipboardData.setData("Text",document.formFb372102.sltNb511812.value);" type="button" value="Copy Formula" name="btCb582026"></form>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 7"]

<tbody>
<form name="formFb372102"></form>[TD="bgcolor: white"]<select onchange="document.formFb372102.txbFb428000.value = document.formFb372102.sltNb511812.value" name="sltNb511812"><option value="=SUM(INDIRECT("C"&SUMPRODUCT((A2:A7=$E2)*(ROW(2:7)))&":C"&SUMPRODUCT((A2:A7=$E2)*(ROW(2:7)))+2))" selected="">F2</option><option value="=SUM(INDIRECT("C"&SUMPRODUCT((A3:A8=$E3)*(ROW(3:8)))&":C"&SUMPRODUCT((A3:A8=$E3)*(ROW(3:8)))+2))">F3</option></select>[/TD]
[TD="bgcolor: #d4d0c8, align: right"] = [/TD]
[TD="bgcolor: white, align: left"]<input size="80" value="=SUM(INDIRECT("C"&SUMPRODUCT((A2:A7=$E2)*(ROW(2:7)))&":C"&SUMPRODUCT((A2:A7=$E2)*(ROW(2:7)))+2))" name="txbFb428000">[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"][/TD]
[TD="bgcolor: #d4d0c8"]<center>A</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>B</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>C</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>D</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>E</center>[/TD]
[TD="bgcolor: #d4d0c8"]<center>F</center>[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>1</center>[/TD]
[TD]Fruit[/TD]
[TD]Type[/TD]
[TD]Total[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>2</center>[/TD]
[TD]Apple[/TD]
[TD]Red[/TD]
[TD]100[/TD]
[TD]*[/TD]
[TD]Apple[/TD]
[TD]430[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>3</center>[/TD]
[TD]Green[/TD]
[TD]150[/TD]
[TD]*[/TD]
[TD]Banana[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>4</center>[/TD]
[TD]Yellow[/TD]
[TD]180[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>5</center>[/TD]
[TD]Banana[/TD]
[TD]Green[/TD]
[TD]60[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>6</center>[/TD]
[TD]Yellow[/TD]
[TD]120[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #d4d0c8"]<center>7</center>[/TD]
[TD]Pink[/TD]
[TD]30[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 7"][TABLE="width: 100%, align: left"]
<tbody>[TR]
[TD="bgcolor: #ffffff, align: left"]Sheet1[/TD]
[TD]*[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>