SUMPRODUCT and merged cells

[TeadyBeeR]

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?

Thanks in advance.

 

[DRSteele]

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.

 

[TeadyBeeR]

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.

 

[DRSteele]

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.

 

[gaz_chops]

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

[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]

 

[TeadyBeeR]

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.

 

[gaz_chops]

Sorry misread your post!

 

[DRSteele]

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