SUMIF and merged cells

J

jasonconlon

Board Regular
Joined
Mar 14, 2002
Messages
80
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?
 
Sort by date Sort by votes
jasonconlon said:
Unfortunately that bombed straight away with "#VALUE!" results..?
(It sure was a lot quicker though. :wink: )
Click to expand...

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.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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..
Click to expand...
Code: 
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...
 
Upvote 0
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*")

Code: 
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
 
Upvote 0
If you know you have problems with certain cells then you could remove those unwanted characters by using the CLEAN and TRIM functions.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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


parry said:
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>
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
3K
Kevin_Li
Kevin_Li
S
  • Solved
A Different way to Calculate Ratios using GDC & or MIN ?
2
Replies
12
Views
536
StrawberryDreams
S
S
  • Locked
  • Question Question
A Different way to Calculate Ratios using GDC & or MIN ?
Replies
1
Views
226
Fluff
Fluff
D
  • Solved
SUMIFS: array for the exclude criteria
Replies
3
Views
424
Georgiboy
Georgiboy
F
  • Question Question
Sumifs with any one or more of multiple criteria
2
Replies
12
Views
1K
Dave Patton
D

Forum statistics

Threads
1,222,835
Messages
6,168,537
Members
452,196
Latest member
rkb1510

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top