+ Reply to Thread
Results 1 to 9 of 9

Counting Merged Cells as multiple values

  1. 08-15-2014, 01:22 AM #1
    jack.stapleton
    jack.stapleton is offline
    Registered User
    Join Date
    08-15-2014
    Location
    Philippines
    MS-Off Ver
    2007
    Posts
    10

    Counting Merged Cells as multiple values

    Hi,

    I have searched around and cannot find a scenario that is as specific to mine with a solution.

    I have a spreadsheet, similar to the one attached, that monitors deliveries. The reasons for deliveries not being made on time are merged across to have one reason per delivery vehicle.

    I am trying to count the number of times a reason occurs, but utilising a COUNTIF function it will only count a merged cell as one count, rather than per each delivery.

    Any help on this would be greatly appreciated.

    Thanks,
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-15-2014, 01:48 AM #2
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Counting Merged Cells as multiple values

    all the experts align when they say ....avoid merged cells where possible

    do not think it is possible to find merged cells normally
    see attached solution using UDF formula
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 08-15-2014, 01:50 AM #3
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Counting Merged Cells as multiple values

    looking at data properly....only 3 delays for delta 3?
    shouldnt you be counting delivery on time instead?
    Reply With QuoteRegister To Reply
  4. 08-15-2014, 01:51 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Counting Merged Cells as multiple values

    HI, welcome to the forum

    working with merged cells is always problematic, if you can use helper columns, we can resolve this relatively easily.

    1. create a helper column (I used J) and copy this down from J2...
    =IF(D2="",J1,D2)
    2. fix the spelling in F4 (you have 3 f's)
    3. Then copy this down from G4,,,
    =COUNTIF($J$2:$J$9,F4&"*")

    You can use a helper to pull out the vehicle in the same manner (I used H)...
    =IF(B2="",H1,B2)
    You can hide these helpers if you want to keep things looking neat and tidy
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 08-15-2014, 01:59 AM #5
    jack.stapleton
    jack.stapleton is offline
    Registered User
    Join Date
    08-15-2014
    Location
    Philippines
    MS-Off Ver
    2007
    Posts
    10

    Re: Counting Merged Cells as multiple values

    Thanks for the prompt response.

    I have utilised the same formula in my actually spreadsheet. But it keeps returning #NAME error.

    Not sure why this would be as it is an exact copy, amended to correct cells, of your example.

    In looking at Calculation steps it appears in my spreadsheet it is not recognising the "ismerged" section of the formula

    Regards,
    Reply With QuoteRegister To Reply
  6. 08-15-2014, 02:29 AM #6
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Counting Merged Cells as multiple values

    Hi Jack

    as mentioned in my post
    i used a UDF (User defined function)

    Please Login or Register  to view this content.
    Ford:
    your helper column takes blanks as being part of merged as well
    so row 7 and 8 takes traffic delays
    Reply With QuoteRegister To Reply
  7. 08-15-2014, 02:41 AM #7
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Counting Merged Cells as multiple values

    solution without using UDF and using fdibbins solution instead
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 08-15-2014, 03:18 AM #8
    jack.stapleton
    jack.stapleton is offline
    Registered User
    Join Date
    08-15-2014
    Location
    Philippines
    MS-Off Ver
    2007
    Posts
    10

    Re: Counting Merged Cells as multiple values

    Thanks to all for the help.

    Problem solved
    Reply With QuoteRegister To Reply
  9. 08-15-2014, 03:49 AM #9
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Counting Merged Cells as multiple values

    not a problem
    please mark thread solved if you have no further questions

    Cheers
    Hum
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Counting merged cells with line breaks
    By Cougar548 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2013, 09:22 AM
  2. [SOLVED] Counting multiple values in single cells
    By guykescheep in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-08-2013, 01:39 PM
  3. Help with counting with conditions and merged cells
    By Rebs in forum Excel General
    Replies: 2
    Last Post: 11-12-2010, 02:04 PM
  4. Counting individual coloured cells that are merged.
    By JohnSan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-06-2008, 01:11 PM
  5. counting colored cells when merged
    By DrKilbert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2005, 03:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1