+ Reply to Thread
Results 1 to 4 of 4

Countif to include any number or specific text in merged cells

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    GA
    MS-Off Ver
    Office Prof. 2019
    Posts
    4

    Countif to include any number or specific text in merged cells

    Okay, this is my first post ever. I can usually find a solution for what I'm trying to do, but this one is pretty unusual.
    First, I want to count the number of cells in a range that contain either a number, OR specific text (PD). To complicate it, the cells in a range aren't contiguous. To complicate it further, the sheet contains merged cells. So even though this is a very simple spreadsheet, I'm having trouble making it do one simple calculation.

    Here's a snipLedgerSnip.JPG

    I know, I know - there are a million ways to make this more efficient, and it's usually a bad idea to merge cells. But I'm trying to drag my low-tech employees past the 20th century use of paper ledger pages for class enrollment. This is something that visually replicates what they're used to. It has some unprotected cells, and a little conditional formatting, but no real calculation, and we can all edit and view on our shared drive. So I'm not really up for changing the format (this year).

    This sheet does exactly what I want, but it'd be really handy if it would count specific cells that contain a number (indicating a payment), or "PD." In H7 is where I'd like the total. It would total these occurrences in (upper-left of merged cell) in H9, H12, H15, H18, etc. Then, at a glance, anyone could see how many paid students are in a given class that month.

    Anyone know any good ways of doing this?

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Countif to include any number or specific text in merged cells

    Hi Brewdaug,

    I'd agree with this previous post at:
    http://www.excelforum.com/excel-form...ged-cells.html

    Get rid of merged cells.

    Looking pretty and being efficient are sometimes at great odds with each other. This would be one of those times.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    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,939

    Re: Countif to include any number or specific text in merged cells

    Hi, welcome to the forum

    While I totally agree with Marvin on this, I know that in the real world, we sometimes cant always have what we want (wow I can hear Mick jagger singing in the background now). Sometimes we just have to try and make the best of a bad situation

    With that in mind, because it's really hard to work with a pic, could you please upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    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

  4. #4
    Registered User
    Join Date
    04-24-2013
    Location
    GA
    MS-Off Ver
    Office Prof. 2019
    Posts
    4

    Re: Countif to include any number or specific text in merged cells

    Yes, I'm afraid I agree with him too. I never use merged cells in "functional" spreadsheets. But this one was more about record keeping than calculations, and the totaling was actually an afterthought. I've found a workaround, albeit a tedious one. In case anyone else is interested, in columns to the right, I used two columns for each month-the first one uses a countif to see if H9 is >0. The next one uses an if function to see if H9 says "PD" and if so, it =1, and I total each column. Then I return that tally to H7. Seems to work perfectly so far. I just hope it doesn't get buggy and quit finding the data in the upper left cell of merged cells down the road.

    But I'm always willing to learn, and there's bound to be a shorter/better way - I'm just a self taught guy that tries to research & solve one of my problems at a time. You may have a much better solution for me. About done working for today, but I'll delete some tabs, and other stuff, and upload a stripped down version tomorrow.

    Thanks to both of you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Don't include hidden cells in COUNTIF formula
    By sam94 in forum Excel General
    Replies: 14
    Last Post: 10-29-2014, 09:39 PM
  2. [SOLVED] CountIF(s) on Merged Cells
    By clprdctn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-23-2014, 01:37 PM
  3. [SOLVED] COUNTIF search range of cells for multiple specific text.
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2013, 06:38 PM
  4. [SOLVED] Count the number of cells with specific text
    By excelinexcel7 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-22-2013, 10:51 AM
  5. Countif with Merged Cells
    By Alexander_Read in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-08-2008, 07:29 AM
  6. Getting range of cells with specific text colour, then using in a COUNTIF formulae
    By cheekyflash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2006, 08:58 PM
  7. [SOLVED] retrieve text from merged cells-How do i read the text in the merged cell?
    By Eric in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2005, 05:05 AM

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