Closed Thread
Results 1 to 4 of 4

SUMPRODUCT and merged cells.

  1. #1
    Forum Contributor
    Join Date
    02-25-2008
    Posts
    101

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe

    =SUMPRODUCT((TEXT(B5:B18,"mmm")=H22)*(B5:B18<>0))
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325
    Quote 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...

  4. #4
    Registered User
    Join Date
    09-26-2010
    Location
    Pakistan
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: SUMPRODUCT and merged cells.

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

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1