+ Reply to Thread
Results 1 to 7 of 7

Formatting & counting cells with same value

  1. #1
    Registered User
    Join Date
    02-06-2014
    Location
    Maui no ka oi
    MS-Off Ver
    MS 365
    Posts
    9

    Formatting & counting cells with same value

    Hi all,

    I have a small table with 4 values. I'd like to take those values and format them into text based on how many values have something in them and if they have the same value or not.

    For example:
    A1 = 12
    B1 = 13
    C1 = blank
    D1 = blank

    I'd like a cell to read "(1) string of (12) & (1) string of (13)"

    Next example:
    A1 = 12
    B1 = 12
    C1 = blank
    D1 = blank

    In this case it should just say "(2) strings of (12)"


    3rd example:
    A1 = 12
    B1 = 12
    C1 = 13
    D1 = blank

    In this case it should just say "(2) strings of (12) & (1) string of (13)"

    4th example:
    A1 = 12
    B1 = 12
    C1 = 13
    D1 = 13

    In this case it should just say "(2) strings of (12) & (2) strings of (13)"


    Final example:
    A1 = 12
    B1 = 12
    C1 = 13
    D1 = 15

    In this case it should just say "(2) strings of (12), (1) string of (13) & (1) string of (15)"




    I'm sure I could figure something out with a long list of IF statements, but I'm hoping there is something simpler.

    Thank you for any advice or help!

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formatting & counting cells with same value

    You will need a string of IF statements, although it could be minimized. Is there a limited set of possible values that could appear? You might end up needing VBA for this if it get complicated.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formatting & counting cells with same value

    After coming up with this I think a UDF would probably be more elegant

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    02-06-2014
    Location
    Maui no ka oi
    MS-Off Ver
    MS 365
    Posts
    9

    Re: Formatting & counting cells with same value

    Thanks folks!

    Richard Buttrey, I tried your formula with:
    A1 = 12
    B1 = 13
    C1 = blank
    D1 = blank

    And the result is:
    (1) string of (12) & (1) string of (13) & (2) strings of (0)

    Any idea on how to remove that "& (2) strings of (0)"
    It seems that anytime that at least one of the cells is blank, it says "(X) strings of (0)"

    Appreciate the effort!

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formatting & counting cells with same value

    That's presumably because C1 & D1 are not blank. Do they contain a zero perchance?

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Formatting & counting cells with same value

    =if(isnumber(a1),countif(a1:a4,a1) & ", ","")&if(and(a2<>a1,isnumber(a2)),countif(a2:a4,a2) & ", ","")&if(and(countif(a1:a2,a3)=0,isnumber(a3)),countif(a3:a4,a3) & ", ","")&if(and(countif(a1:a3,a4)=0,isnumber(a4)),1 ,"")
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  7. #7
    Registered User
    Join Date
    02-06-2014
    Location
    Maui no ka oi
    MS-Off Ver
    MS 365
    Posts
    9

    Re: Formatting & counting cells with same value

    No there wasn't a zero, but I decided to go a much simpler route:

    First cell I used: ="String 1: ("A1") modules"
    2nd cell: =IF('B1="","","String 2: ("B1&") modules")
    3rd cell: =IF('C1="","","String 3: ("C1&") modules")
    4th cell: =IF('D1="","","String 4: ("D1&") modules")

    Not exactly what I had wanted, but this way is way simpler than what I originally wanted.

    Thanks for all the effort guys! You are all amazing!

+ 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. [SOLVED] Counting cells with conditional formatting
    By GregCM in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-09-2016, 06:10 PM
  2. counting colored cells while using contitional formatting
    By okg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2011, 02:50 PM
  3. Counting Cells with Conditional Formatting
    By jcherian in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-01-2008, 06:03 PM
  4. [SOLVED] counting cells based on formatting
    By dwae2000 in forum Excel General
    Replies: 10
    Last Post: 07-12-2006, 09:45 PM
  5. [SOLVED] Counting cells based on formatting
    By JRJ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2006, 06:30 PM
  6. Counting Cells with Conditional Formatting
    By JasonC in forum Excel General
    Replies: 6
    Last Post: 12-30-2005, 01:35 AM
  7. [SOLVED] counting cells with conditional formatting applied
    By HalB in forum Excel General
    Replies: 3
    Last Post: 02-21-2005, 10:06 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