SUMPRODUCT and merged cells.

Hiya,


I have a problem that keeps popping up. I've usually managed to get around it in really makeshift ways, but it's annoying me and I'm sure there's a simple way to deal with it.

Basically, if an array in a SUMPRODUCT formula contains merged cells, it does some strange things.... To explain what I mean I have done a simple spreadsheet (attached).

The user types things into only the light blue cells. You'll see that column B automatically says the month of the expense. But if I want to see how many January expenses there are, it counts the second parts of the merged cells as January.

Any idea how to correct this?

thanks

Originally Posted by skatmandu2002

Hiya,


I have a problem that keeps popping up. I've usually managed to get around it in really makeshift ways, but it's annoying me and I'm sure there's a simple way to deal with it.

Basically, if an array in a SUMPRODUCT formula contains merged cells, it does some strange things.... To explain what I mean I have done a simple spreadsheet (attached).

The user types things into only the light blue cells. You'll see that column B automatically says the month of the expense. But if I want to see how many January expenses there are, it counts the second parts of the merged cells as January.

Any idea how to correct this?

thanks

Also consider replacing merged cells with " center across selection".
Merged cells are a real pain...

=sumproduct(--(text(b5:b18,"mmm")=h22),g5:g18)