# SUMPRODUCT and merged cells

##### New Member
Hello all,

I have a problem with my SUMPRODUCT formula caused by the fact that I am using merged cells.
My excel table looks like this (cells A2/A3 and A4/A5 are merged):

 Projects Name Name 2 John Bob 3 Bob John

<tbody>
</tbody>

And my formula are:
=SUMPRODUCT((\$B\$2:\$C\$5="John")*(\$A\$2:\$A\$5))
=SUMPRODUCT((\$B\$2:\$C\$5="Bob")*(\$A\$2:\$A\$5))

I want to count the number of projects assigned to Bob and John, but their name could be put in different cells.
In the above example it will tell me that Bob is assigned to 5 projects (correct), but it will tell me that John is only assigned to 2 because his name is put in the cell that is in the lower part of the merged cell and is not taken into consideration.

Is there a way to fix this somehow or use a different formula to count it?

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I would guess that the problem is unrelated to the merging of the cells; rather, the problem is that there is no '3' in cell A5 beside John's name.

There is a '3' next to John's name. It doesn't show on my "picture", but like I have said, A2/A3 and A4/A5 are merged, so that '3' is next to both Bob and John but the formula acts as if A5 was empty.

All formulas will see your A3 and A5 as empty. Unless I'm mistaken you might need to un-merge those cells and use a different design.

Try this
=SUMPRODUCT(--(ISNUMBER(SEARCH("Bob",\$B\$2:\$B\$5)))*(\$A\$2:\$A\$5))

Code:
``````[TABLE="width: 195"]
<tbody>[TR]
[TD="width: 65"]Projects[/TD]
[TD="width: 65"]Name[/TD]
[TD="width: 65"]Name[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="colspan: 2"]John Bob[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="colspan: 2"]Bob John[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="colspan: 2"]Fred John[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="colspan: 2"]Jim Bob[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]``````

Last edited:
Try this
=SUMPRODUCT(--(ISNUMBER(SEARCH("Bob",\$B\$2:\$B\$5)))*(\$A\$2:\$A\$5))

Unfortunately this seems to be working exactly the same way I did my original formula. I guess my table in the original post is confusing (that's how it was pasted from my document), but in your example cells in the Projects column are not merged. To make it similar to my problem you would need to merge the cells containing 2 and 3 into one cell as well as cells containing 6 and 4.

Thanks for trying to help anyway.

All formulas will see your A3 and A5 as empty. Unless I'm mistaken you might need to un-merge those cells and use a different design.

This is exactly my problem
I really don't want to un-merge those cells and/or redesign the table (it's a little bit more complex than what I have shown here).

Thanks for help anyway.

You're welcome. I think if you post more data using the Mr Excel HTML Maker we can help more.

Replies
1
Views
78
Replies
7
Views
399
Replies
8
Views
431
Replies
8
Views
54
Replies
6
Views
180

1,207,094
Messages
6,076,550
Members
446,212
Latest member
KJAYPAL200

### 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.

### Which adblocker are you using?

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

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